Notification Examples
Contents
Defect state updates
Send notification to submitter ('Submitter' CQ field - reference to users) when Defect is postponed (with action postpone), rejected (with action reject) or closed (action close).
Do not send notification if submitter is the same user that perform action to avoid email flooding.
We have to test notification rule in test database and migrate it to production without changes (database name in http link should be updated automatically).
We will define two supplementary functions in database properties:
udb_property records:
Name |
nm_value |
---|---|
GetUserLoginName |
return $session->GetUserLoginName();
|
GetCurrentDatabaseName |
my $dbname = $session->GetSessionDatabase()->GetDatabaseName();
return $dbname;
|
Email_Notification_Rule fields:
Form Field Name (CQ field name) |
Value |
---|---|
Name (name) |
State change submitter notification (any unique name) |
Record Type (Entity_Def) |
Defect |
Actions (actions) |
Postpone Reject Close |
Condition (Msg_Condition) |
GetUserLoginName() ne $Submitter |
To (Msg_To_Users) |
$Submitter.email |
Subject (Msg_Subject) |
Your Defect '$ID' has been ${state}. |
Body (Msg_Body) |
Your Defect '$ID' has been ${state}. Defect title: '$Headline' http://newcqweb-server/cqweb/main?USE_CASE=GO&service=CQ&schema=DBSET&contextid=GetCurrentDatabaseName()&entityID=${dbid}&entityDefName=Defect |
Subject will be evaluated when email notification is triggered, for example:
"Your Defect 'SAMPL00000001' has been Rejected."
We use ${State} notation for state field to avoid ambiguity, because it is followed by '.' (valid symbol for variable name).
Starting version 1.5, $UserLoginName predefined package variable can be used instead of GetUserLoginName user-defined function
Using SQL query
Send notification to clearquest user group 'CCB_Approvers' and submitter when request been resolved
Form Field Name (CQ field name) |
Value |
---|---|
Name (name) |
Resolution Notification (any unique name) |
Record Type (Entity_Def) |
Defect |
Actions (actions) |
Resolve |
To (Msg_To_Users) |
$Submitter.email SQL("SELECT T2.email FROM parent_child_links T1, groups T3, users T2 WHERE T1.parent_dbid = T2.dbid AND T1.child_dbid = T3.dbid AND T3.name = 'CCB_Approvers' AND T2.is_active = 1") |
Subject (Msg_Subject) |
Defect '$ID' has been resolved. Resolution '$Resolution' |
Body (Msg_Body) |
Defect '$ID' has been resolved. Defect title: '$Headline' Resolution: '$Resolution' Description: $Description http://newcqweb-server/cqweb/main?USE_CASE=GO&service=CQ&schema=DBSET&contextid=GetCurrentDatabaseName()&entityID=${dbid}&entityDefName=Defect |
You can also create reusable function and put it into properties
udb_property record
Name |
nm_value |
---|---|
GetGroupEmailAddresses |
my ($groupname) = @_;
my $return_string;
# SQL statement
my $result_set = $session->BuildSQLQuery(
"SELECT T2.email
FROM parent_child_links T1, groups T3, users T2
WHERE T1.parent_dbid = T2.dbid
AND T1.child_dbid = T3.dbid
AND T3.name = '$groupname'
AND T2.is_active = 1");
$result_set->Execute();
# fetch email addresses
while( $result_set->MoveNext() == $CQPerlExt::CQ_SUCCESS ){
my $tmp_str = $result_set->GetColumnValue(1);
$return_string .= "$tmp_str\n";
}
# clear 'errors'
$result_set = undef;
$@ = '';
return $return_string;
|
In this case 'To' field in Notification rule could be changed to:
Form Field Name (CQ field name) |
Value |
---|---|
To (Msg_To_Users) |
$Submitter.email GetGroupEmailAddresses( 'CCB_Approvers' ) |
SQL query and dynamically assigned message priority
To simplify defect management, we use assignment or functional group idea.
Defect submitters do not know who is responsible for particular feature, but can guess, what functional area is.
Submitter needs to select Assignment group only.
We need to notify Assignment group manager(s) about new defect assigned to group.
If defect Severity is '1-Critical' or Priority is '1-Resolve Immediately', email message has to be flagged as 'High Priority'.
Possible AssignmentGroups stateless record type implementation:
Name | SHORT_STRING |
Unique group name |
Managers |
REFERENCE to users |
group managers |
Users |
REFERENCE to users | group members |
Active | INT |
obsolete groups marked as '0', valid groups as '1' |
Defect record type has Assigned_To_Group field (reference to AssignmentGroups).
Form Field Name (CQ field name) |
Value |
---|---|
Name (name) |
Notify Group Managers (any unique name) |
Record Type (Entity_Def) |
Defect |
Field Change (Field_Change) |
Assigned_To_Group |
Priority (Msg_Priority) |
$Priority =~ /^1/ || $Severity =~ /^1/ |
To (Msg_To_Users) |
SQL("SELECT T3.email FROM parent_child_links T1, fielddef T2, users T3, assignmentgroups T4 WHERE T1.parent_dbid = T4.dbid AND T1.child_dbid = T3.dbid AND T1.parent_fielddef_id = T2.id AND T2.name = 'Managers' AND T4.name = '${Assigned_To_Group}' ") |
Subject (Msg_Subject) |
Defect '$ID' with severity: '$Severity' and priority: '$Priority' has been assigned to your team. Please assign it to somebody in your team. |
Body (Msg_Body) |
Defect '$ID' has been assigned to your team. Please assign Defect to somebody in your team or re-assign it to another team if initial assignment was incorrect. Defect title: '$Headline' Severity: '$Severity' Priority: '$Priority' Description: $Description http://newcqweb-server/cqweb/main?USE_CASE=GO&service=CQ&schema=DBSET&contextid=GetCurrentDatabaseName()&entityID=${dbid}&entityDefName=Defect |
Using original field value
Starting version 1.4, original value can be accessed using GetFieldOriginalValue package-defined function, but this example can be used in earlier versions of the package.
Notify assignee that defect priority assigned to him has been raised. Valid priorities are '1- something', '2 - something', etc. That simplifies comparison.
Lets create user-defined function that retrieves previous value for field.
udb_property record
Name |
nm_value |
---|---|
GetFieldOriginalValue |
my $name = shift;
my $value = '' ;
eval {
$value = $entity->GetFieldOriginalValue($name)->GetValue();
};
$@ = '';
return $value;
|
Notification rule:
Form Field Name (CQ field name) |
Value |
---|---|
Name (name) |
Priority has been raised - notify assignee |
Record Type (Entity_Def) |
Defect |
Field Change (Field_Change) |
Priority |
Condition (Msg_Condition) |
$Priority gt GetFieldOriginalValue('Priority') |
To (Msg_To_Users) |
$owner.email |
Subject (Msg_Subject) |
Priority has been raised for defect '$ID' from 'GetFieldOriginalValue('Priority')' to '$Priority' |
Body (Msg_Body) |
Priority has been raised for defect '$ID' from 'GetFieldOriginalValue('Priority')' to '$Priority' Defect title: '$Headline' Severity: '$Severity' Priority: '$Priority' Description: $Description http://newcqweb-server/cqweb/main?USE_CASE=GO&service=CQ&schema=DBSET&contextid=GetCurrentDatabaseName()&entityID=${dbid}&entityDefName=Defect |
Sending HTML Emails
We need to specify proper content type in the message header ("Header add-in" field). When it is done, we can use HTML in the message body, highlighting text, using different fonts and colors, etc.
Notification rule:
Form Field Name (CQ field name) |
Value |
---|---|
Name (name) |
My first HTML email |
Record Type (Entity_Def) |
Defect |
Header add-in (Msg_Header) |
Content-Type: text/html; charset=us-ascii |
To (Msg_To_Users) |
$owner.email |
Subject (Msg_Subject) |
HTML test message for defect '$ID' |
Body (Msg_Body) |
<HTML> <BODY>
<H2 ALIGN="left">$EntityDefName $ID</H2>
<TABLE ALIGN="left" BORDER="1">
<TR><TD><b>ID</b> </TD> <TD>$id </TD></TR>
<TR><TD><b>headline</b></TD> <TD>$Headline</TD></TR>
<TR><TD><b>Priority</b></TD> <TD>$Priority</TD></TR>
<TR><TD><b>Severity</b></TD> <TD>$Severity</TD></TR>
<TR><TD><b>Description</b></TD>
<TD><PRE>$Description</PRE> </TD></TR>
</TABLE> </BODY> </HTML>
|
Sending Emails with Attachments
Fist of all, it is not recommended to attach large files to email messages. It might significantly impact your application performance. It would work better if you could send an URL to the record, or the file, but sometimes, attaching files to emails is the only solution.
In this case, we will define two supplementary functions: one to check for attachments, and another to add attachments to email.
udb_property records:
Name |
nm_value |
---|---|
RT_HasAttachments |
# checks for attachment on current entity
# returns true or false respectively
my ($result, $i);
my $att_fields = $entity->GetAttachmentFields();
# check for attachments in all Attachment type fields
for($i = 0; $i < $att_fields->Count(); $i++ ){
my $att_field = $att_fields->Item($i);
if($att_field->GetAttachments()->Count() > 0 ){
$result = 1; # attachment exists
last;
}
}
# TRUE if current record has attachment
return $result;
|
RT_InsertAttachments |
# add attachments to email
require MIME::Base64;
my ($result, $tmp, $attachments, $attachment, $file, $tmpfile, $nload, $i, $k, $num);
# get all fields of attachment type
my $att_fields = $entity->GetAttachmentFields();
my $dbid = $entity->GetFieldValue('dbid')->GetValue();
# create unique boundary separator
my $boundary = "Boundary_($dbid)";
# get temporary directory location where to extract attachments
$tmp .= '/' if (($tmp = $ENV{TMP}) ne '' || ($tmp = $ENV{TEMP}) ne '');
$tmp = '/tmp/' if ( $tmp eq '' && $^O ne 'MSWin32' );
for($i = 0; $i < $att_fields->Count(); $i++ ){
# for all attachment fields:
my $att_field = $att_fields->Item($i);
if( ($num = ($attachments = $att_field->GetAttachments())->Count()) > 0 ){
for($k=0; $k < $num; $k++){
# for all attachments in the field
# get attachment
$attachment = $attachments->Item($k);
# get the file name
$file = $attachment->GetFileName();
if( $file =~ /[\\\/]/ ){
# if the file has path delimiters – it has not been loaded yet
# (file attached in current action?)
# we can use its original location
$tmpfile = $file;
# remove path delimiters
# and set ‘do not load’ flag
$file =~ s/.*[\\\/]//;
$nload = 1;
}else{
# otherwise - create a temporary file name
$tmpfile = "$tmp$dbid.$k.txt";
# unset ‘do not load’ flag
$nload = 0;
}
# load attachment from record into temporary file
if( $nload || $attachment->Load($tmpfile) ){
# open the attachment file
open(TMPFILE, $tmpfile) or die "Cannot open file '$tmpfile'\n";
# set file handle to binary mode
binmode TMPFILE;
# unset record delimiter
local $/ = undef;
# encode content of the file with Base64 encoding
my $content = MIME::Base64::encode_base64(<TMPFILE>);
# close the file
close(TMPFILE);
# and delete it, if it was a temporary file
# created to save the attachment
unlink($tmpfile) unless $nload;
# add header and encoded attachment to the message
$result .= "
--$boundary
Content-type: application/octet-stream
Content-transfer-encoding: base64
Content-disposition: attachment; filename=$file
$content\n";
}
}
}
}
# return encoded attachment
return $result;
|
Email_Notification_Rule fields:
Form Field Name (CQ field name) |
Value |
---|---|
Name (name) |
Sample notification with attachments |
Record Type (Entity_Def) |
Defect |
Header Add-in (Msg_Header) |
IF( RT_HasAttachments(), "MIME-Version: 1.0
Content-type: multipart/mixed;
boundary=\"Boundary_\($dbid)\"")
|
To (Msg_To_Users) |
$Submitter.email |
Subject (Msg_Subject) |
Defect '$ID' has been ${state}. |
Body (Msg_Body) |
IF( RT_HasAttachments(), "--Boundary_\($dbid)
Content-type: text/plain; charset=us-ascii")
Defect $ID
Headline: $Headline
Description: $Description
RT_InsertAttachments()
|
Sending Consolidated Queries or Reports Using Timed Notification
Timed notification was created to trigger alerts for individual records, but can it be used to send consolidated reports? There are few ways to achieve it. If you are using CQWeb 7.1 or later, you can employ data-pull reports and send URL to the users. No notification would be ever required in this case. Another way would be to create scheduled notification to run some queries (or reports) and send results to the users. First of all, I would like to create new record type, scheduled_queries, that might look like this
Field Name | Field Type | Description |
---|---|---|
Name | SHORT_STRING | unique identifier of the scheduled query |
Frequency | SHORT_STRING | choice list: daily, weekly, monthly, etc |
Active | INT | active/inactive 1/0 checkbox to deactivate entry without removing it from the database |
Path | MULTILINE_STRING | path to the query in the workspace. Multiline type is preferred to avoid 254 symbols limitation |
After creating the record type, applying Email Notification package to the record, and upgrading user database, you can configure timed notifications for the new record type. The scope can include all active records of the new record type. No condition is required. The following user-defined function can be used to execute query and extract query result in the form of HTML table (we can call it "RETURN_QUERY_RESULTS_AS_HTML_TABLE"):
Name |
nm_value |
---|---|
RETURN_QUERY_RESULTS_AS_HTML_TABLE |
my $query_path = shift;
# change path to UNIX style
$query_path =~ s/\\+/\//g;
# resulting HTML table
my $table = "<TABLE BORDER=\"1\" CELLPADDING=\"10\">\n";
# query fields
my @fields = ();
# get workspace
my $ws = $session->GetWorkSpace();
# get query definition
my $qdef = $ws->GetQueryDef($query_path);
my $field_defs = $qdef->GetQueryFieldDefs();
my $count = $field_defs->Count();
for( my $i = 1; $i < $count; $i++ ){
my $field_def = $field_defs->Item($i);
my $name = $field_def->GetLabel();
my $type = $field_def->GetFieldType();
push(@fields, { name => $name, type => $type });
}
# Create Table Header row
$table .= '<TR>';
foreach my $field (@fields){
$table .= "<TH><b>$field->{name}</b></TH>";
}
$table .= "</TR>\n";
# execute query
my $resultset = $session->BuildResultSet($qdef);
$resultset->Execute();
# create data rows
while( $resultset->MoveNext() == $CQPerlExt::CQ_SUCCESS ){
$table .= '<TR>';
for( my $i = 2; $i <= $count; $i++ ){
my $value = $resultset->GetColumnValue($i);
$table .= "<TD>$value</TD>";
}
$table .= "</TR>\n";
}
$table .= "</TABLE>\n";
#return HTML table
$@ = '';
return $table;
|
Then we can use it in the timed notification rule body field, something like:
Form Field Name (CQ field name) |
Value |
---|---|
Header Add-in (Msg_Header) |
Content-type: text/plain; charset=us-ascii
|
Body (Msg_Body) |
<HTML><BODY>
something you would like to to say about scheduled query $Name
RETURN_QUERY_RESULTS_AS_HTML_TABLE($Path)
</BODY></HTML>
|
Timed Notification triggers the rule, all query records are selected, query executed and formatted as HTML table, and email is sent to the users. From that moment, it is easy to maintain: you can add new scheduled queries just by submitting new records of the "scheduled queries" record type and specifying new query path, and you can update content of the emails by editing queries in the workspace.