Difference between revisions of "Notification Examples"

Latest revision as of 14:44, 1 February 2013

Examples

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.

@@ Line 1: / Line 1: @@
 <center><big>'''<big>Examples</big>'''</big><br /></center>
-== Defect state updates ==
+== '''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).<br /> Do not send notification if submitter is the same user that perform action to avoid email flooding.<br /> 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).<br /><br />
@@ Line 49: / Line 49: @@
 <br /> Subject will be evaluated when email notification is triggered, for example:<br /> "Your Defect 'SAMPL00000001' has been Rejected."<br /> We use ${State} notation for state field to avoid ambiguity, because it is followed by '.' (valid symbol for variable name).<br />
-Starting version 1.5, '''$UserLoginName''' predefined package variable can be used instead of GetUserLoginName user-defined function<br /><br />
+Starting version 1.5, '''$UserLoginName''' predefined package variable can be used instead of GetUserLoginName user-defined function<br /><br /><br />
-== Using SQL query ==
+== '''Using SQL query''' ==
 Send notification to clearquest user group 'CCB_Approvers' and submitter when request been resolved<br />
@@ Line 120: / Line 120: @@
 <br /><br />
-== SQL query and dynamically assigned message priority ==
+== '''SQL query and dynamically assigned message priority''' ==
 To simplify defect management, we use assignment or functional group idea.<br /> Defect submitters do not know who is responsible for particular feature, but can guess, what functional area is.<br /> Submitter needs to select Assignment group only.<br /> We need to notify Assignment group manager(s) about new defect assigned to group.<br /> If defect Severity is '1-Critical' or Priority is '1-Resolve Immediately', email message has to be flagged as 'High Priority'.<br /><br /> Possible AssignmentGroups stateless record type implementation: <br />
@@ Line 171: / Line 171: @@
-== Using original field value ==
+== '''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.<br /> Lets create user-defined function that retrieves previous value for field.<br /> udb_property record<br />
@@ Line 220: / Line 220: @@
 <br /><br />
-== Sending HTML Emails ==
+== '''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.
@@ Line 257: / Line 257: @@
 </TABLE> </BODY> </HTML> </source>
 |}
+<br /><br />
+== '''Sending Emails with Attachments''' ==
-== 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.
@@ Line 400: / Line 400: @@
 <br /><br />
-[[Category:Email_Notification_Package]]
+== '''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
+{| width="100%" border="1" cellspacing="2" cellpadding="2"
+! width="20%" valign="top" | Field Name
+! width="20%" valign="top" | Field Type
+! valign="top" | Description
+|-
+| width="20%" valign="top" | '''Name'''
+| valign="top" | SHORT_STRING
+| valign="top" |  unique identifier of the scheduled query
+|-
+| width="20%" valign="top" | '''Frequency'''
+| valign="top" | SHORT_STRING
+| valign="top" |  choice list: daily, weekly, monthly, etc
+|-
+| width="20%" valign="top" | '''Active'''
+| valign="top" | INT
+| valign="top" |  active/inactive 1/0 checkbox to deactivate entry without removing it from the database
+|-
+| width="20%" valign="top" | '''Path'''
+| valign="top" | MULTILINE_STRING
+| valign="top" |  path to the query in the workspace. Multiline type is preferred to avoid 254 symbols limitation
+|}
+<br />
+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"):
+{| width="100%" border="1" cellspacing="2" cellpadding="2"
+! width="20%" valign="top" | Name<br />
+! valign="top" | nm_value<br />
+|-
+| width="20%" valign="top" | RETURN_QUERY_RESULTS_AS_HTML_TABLE<br />
+| valign="top" | <source lang="perl">
+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;</source>
+|}
+<br />
+Then we can use it in the timed notification rule body field, something like:
+{| width="100%" border="1" cellspacing="2" cellpadding="2"
+! width="20%" valign="top" | Form Field Name<br /> (CQ field name)<br />
+! valign="top" | Value<br />
+|-
+| width="20%" valign="top" | Header Add-in<br /> (Msg_Header)<br />
+| valign="top" |  <source lang="text">Content-type: text/plain; charset=us-ascii</source>
+|-
+| valign="top" | Body<br /> (Msg_Body)<br />
+| valign="top" |  <source lang="text">
+<HTML><BODY>
+something you would like to to say about scheduled query $Name
+RETURN_QUERY_RESULTS_AS_HTML_TABLE($Path)
+</BODY></HTML>
+</source><br /><br />
+|}
+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.
+<br />
+[[Category:Rational]] [[Category:Email_Notification_Package]]

Difference between revisions of "Notification Examples"

Latest revision as of 14:44, 1 February 2013

Contents

Defect state updates

Using SQL query

SQL query and dynamically assigned message priority

Using original field value

Sending HTML Emails

Sending Emails with Attachments

Sending Consolidated Queries or Reports Using Timed Notification

Navigation menu

Page actions

Page actions

Personal tools

Navigation

Search

Tools