Difference between revisions of "Notification Examples"

From cqwiki
Jump to navigationJump to search
Line 401: Line 401:
  
  
−
== '''Sending Consolidated Queries of Reports Using Timed Notification''' ==
+
== '''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?  
 
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.
 
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.

Revision as of 18:51, 5 June 2012

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.


Retrieved from "https://cqadmin.org/w/index.php?title=Notification_Examples&oldid=137"