Topic
  • 21 replies
  • Latest Post - ‏2014-02-27T13:36:09Z by RajaniMatta
RajaniMatta
RajaniMatta
12 Posts

Pinned topic Perl script for Submit Date action notification

‏2014-02-11T11:38:32Z |

Greetings,

I am trying to add a notification PERL or BASIC script on submit date, This should be the condition:

Send an email to the owners of the record type, if the record type is still in "Submit" state 30 days after submission.

I am unable to add the condition into the PERL script. Can you please look into this. 

Thank You in advance.

sub submit_date_Validation {

    my($submit_date) = @_;

    my $result

    # $fieldname as string scalar

    # record type name is BCR

    # field name is Submit_Date

    # Start User Code

    # Return a non-empty string explaining why the field's current

    # value is not valid. If it is valid, return an empty string.

    # End User Code

     $submit_date ne ''  $submit_date && DateTimeDiff($submit_date, GetDateTime(), 'd' ) > 30

   ${owner.email}

    return $submit_date;

}

Updated on 2014-02-11T15:28:06Z at 2014-02-11T15:28:06Z by RajaniMatta
  • pdubovitsky
    pdubovitsky
    376 Posts

    Re: Perl script for Submit Date action notification

    ‏2014-02-11T15:29:43Z  

    Hello,

    You can use Timed Notification feature of the ClearQuest Email Notification Package.

    Pavel

  • RajaniMatta
    RajaniMatta
    12 Posts

    Re: Perl script for Submit Date action notification

    ‏2014-02-11T18:36:52Z  

    Hello,

    You can use Timed Notification feature of the ClearQuest Email Notification Package.

    Pavel

    Thanks Pavel. How would I get that...I am using CQ 7.1.2 and tried installin email plus package.

    I was able to apply package fine but unable to see the new "email plus records" in ClearQuest after upgrading the DB.

     

  • pdubovitsky
    pdubovitsky
    376 Posts

    Re: Perl script for Submit Date action notification

    ‏2014-02-11T19:36:01Z  

    Thanks Pavel. How would I get that...I am using CQ 7.1.2 and tried installin email plus package.

    I was able to apply package fine but unable to see the new "email plus records" in ClearQuest after upgrading the DB.

     

    I don't think that Email Plus could be helpful. I am talking about different ClearQuest package, Email Notification.
    It allows you to implement customized email notifications, including reminders.

    Pavel

  • RajaniMatta
    RajaniMatta
    12 Posts

    Re: Perl script for Submit Date action notification

    ‏2014-02-11T20:38:44Z  

    I don't think that Email Plus could be helpful. I am talking about different ClearQuest package, Email Notification.
    It allows you to implement customized email notifications, including reminders.

    Pavel

    I applied the email notification package but I do not see that email notification record..like File-New-Email Rule in ClearQuest.
  • pdubovitsky
    pdubovitsky
    376 Posts

    Re: Perl script for Submit Date action notification

    ‏2014-02-11T21:05:54Z  
    I applied the email notification package but I do not see that email notification record..like File-New-Email Rule in ClearQuest.

    Are you able to see the new record type in ClearQuest designer?
    Did you upgrade the database to the new version of schema?

    Pavel

  • RajaniMatta
    RajaniMatta
    12 Posts

    Re: Perl script for Submit Date action notification

    ‏2014-02-11T21:47:21Z  

    Are you able to see the new record type in ClearQuest designer?
    Did you upgrade the database to the new version of schema?

    Pavel

    Yes, I am seeing the new record type in CQ designer, but not on CQ Client. Is there a way I can re-do this, uninstall the package and re-install.
  • pdubovitsky
    pdubovitsky
    376 Posts

    Re: Perl script for Submit Date action notification

    ‏2014-02-11T21:56:32Z  
    Yes, I am seeing the new record type in CQ designer, but not on CQ Client. Is there a way I can re-do this, uninstall the package and re-install.

    1. Please make sure that you followed the installation instructions (starting step 4 after installing the package)
    http://cqadmin.org/wiki/Package_installation#Start_ClearQuest_Designer_Package_Wizard
    2. Verify that you applied package to the correct schema.
    3. Upgrade ClearQuest user database
    4. Login as super user to the user database
    Then you should be able to create Email_Notification_Rule records
     

    Pavel

  • DonaldN
    DonaldN
    282 Posts

    Re: Perl script for Submit Date action notification

    ‏2014-02-11T23:57:54Z  

    Using a validation hook for this purpose requires the record in question to be "touched"? Is it true in your use case? What if a submitted record is left unnoticed for months?

    Similar questions came up in the past and I always suggest running a schedule job (daily or whatever the frequency you desire) to filter out such records.

    Basically in the schedule job (an external Perl script for example), you will do the following.

    1. Run a query, pre-defined or coded in the script, to get the records in "submitted" state and last updated 30 days ago.

    2. For each record in the result set, send an email to the submitter.

  • CQAdmin
    CQAdmin
    103 Posts

    Re: Perl script for Submit Date action notification

    ‏2014-02-12T02:52:15Z  

    As mentioned by Donald; THE Best way is to write a Perl script using ClearQuest APIs and execute this script using scheduler on daily basis.

    The below tech note is not exactly match with your requirement, but it will give you a Start..

    http://www-01.ibm.com/support/docview.wss?uid=swg21649015

    And the below Tech Note shows how you can run a specific query which is pre-defined in your CQ Client.

    http://www-01.ibm.com/support/docview.wss?uid=swg21639941

    Using these two tech notes you can try writing your own Perl Script and then add a code to send email to certain users.

  • RajaniMatta
    RajaniMatta
    12 Posts

    Re: Perl script for Submit Date action notification

    ‏2014-02-12T17:43:36Z  

    1. Please make sure that you followed the installation instructions (starting step 4 after installing the package)
    http://cqadmin.org/wiki/Package_installation#Start_ClearQuest_Designer_Package_Wizard
    2. Verify that you applied package to the correct schema.
    3. Upgrade ClearQuest user database
    4. Login as super user to the user database
    Then you should be able to create Email_Notification_Rule records
     

    Pavel

    Package issues resolved. the user groups were not set.

    Thanks Pavel.

  • RajaniMatta
    RajaniMatta
    12 Posts

    Re: Perl script for Submit Date action notification

    ‏2014-02-12T18:30:52Z  
    • DonaldN
    • ‏2014-02-11T23:57:54Z

    Using a validation hook for this purpose requires the record in question to be "touched"? Is it true in your use case? What if a submitted record is left unnoticed for months?

    Similar questions came up in the past and I always suggest running a schedule job (daily or whatever the frequency you desire) to filter out such records.

    Basically in the schedule job (an external Perl script for example), you will do the following.

    1. Run a query, pre-defined or coded in the script, to get the records in "submitted" state and last updated 30 days ago.

    2. For each record in the result set, send an email to the submitter.

    Hi Donald,

    Thanks. right, validation hooks need to have an action to trigger. 

    I will be querying on 2 conditions in Email notification condition 

    1) Left in submitted state for >20 days and

    2)Left in modified state for >20 days.

    I think will write a SQL script to get the data from 2 or 3 tables and have a scheduler run every day to send auto notification.

    Thanks,

    Rajani

  • CQAdmin
    CQAdmin
    103 Posts

    Re: Perl script for Submit Date action notification

    ‏2014-02-14T05:32:50Z  

    Here is the Perl script which can be scheduled to run on a specific time using "Windows Scheduler".

    This script is valid for "Defect" record type. It finds all the Defects which are in "Submitted" state and older than 30 days. Then it sends email notification to "Owner" of that Defect.

    You can modify it as per your needs before using it...

     

    #!C:\Program Files\IBM\RationalSDLC\ClearQuest\CQperl.exe
    use CQPerlExt;
    my $body;
    my $sessionObj = CQSession::Build();

    $sessionObj->UserLogon("admin", "<AdminPasswd>", "<UserDB>", "<Repository>");
    my $sqlstring = "select distinct T1.dbid,T1.id,T3.name,T8.email,T1.submit_date from Defect T1,statedef T3,users T8 where T1.state = T3.id and T1.owner = T8.dbid and (T1.dbid <> 0 and (T3.name = 'Submitted'))";
    my $ResultSet = $sessionObj-> BuildSQLQuery($sqlstring);

    $ResultSet->EnableRecordCount();
    $ResultSet->Execute();
    $recordCount = $ResultSet->GetRecordCount();

    for ($i=0;$i<$recordCount;$i++)
    {
    $status = $ResultSet->MoveNext();
    $ClearQuestID[$i]= $ResultSet->GetColumnValue(2);
    $State[$i] = $ResultSet->GetColumnValue(3);
    $OwnerEmail[$i] = $ResultSet->GetColumnValue(4);
    $SubmitDate[$i] = $ResultSet->GetColumnValue(5);
    my $SubDate = substr($SubmitDate[$i], 0, 10);
    my $days = 30 * 24 * 60 * 60;
    my ($old_day, $old_month, $old_year) = (localtime(time - $days))[3..5];
    my $cutoffDate = sprintf('%04d-%02d-%02d',$old_year + 1900, $old_month + 1, $old_day);

    if($SubDate lt $cutoffDate)
    {
    $body = "Below Defect is Submitted for more than 30 days.\n\n";
    $body .= "ID -> $ClearQuestID[$i]\n";
    my $mailmsg = CQMailMsg::Build();
    my @SMTPemailsettings = ('SMTP',"<EmailServer>",'do.not.reply@test.com','CQ Admin','1');
    my $setmail_success = $mailmsg->SetMailNotificationSettings(\@SMTPemailsettings);
    my $emailsettings = $mailmsg->GetMailNotificationSettings();
    my @emailsettings = @$emailsettings;
    $mailmsg->AddTo("$OwnerEmail[$i]");
    $mailmsg->SetSubject("Defects submitted for 30 days");
    $mailmsg->SetBody("$body");
    $mailmsg->Deliver();
    CQMailMsg::Unbuild($mailmsg);
    }
    }
    CQPerlExt::CQSession_Unbuild($sessionObj);

  • RajaniMatta
    RajaniMatta
    12 Posts

    Re: Perl script for Submit Date action notification

    ‏2014-02-25T20:46:02Z  
    • CQAdmin
    • ‏2014-02-14T05:32:50Z

    Here is the Perl script which can be scheduled to run on a specific time using "Windows Scheduler".

    This script is valid for "Defect" record type. It finds all the Defects which are in "Submitted" state and older than 30 days. Then it sends email notification to "Owner" of that Defect.

    You can modify it as per your needs before using it...

     

    #!C:\Program Files\IBM\RationalSDLC\ClearQuest\CQperl.exe
    use CQPerlExt;
    my $body;
    my $sessionObj = CQSession::Build();

    $sessionObj->UserLogon("admin", "<AdminPasswd>", "<UserDB>", "<Repository>");
    my $sqlstring = "select distinct T1.dbid,T1.id,T3.name,T8.email,T1.submit_date from Defect T1,statedef T3,users T8 where T1.state = T3.id and T1.owner = T8.dbid and (T1.dbid <> 0 and (T3.name = 'Submitted'))";
    my $ResultSet = $sessionObj-> BuildSQLQuery($sqlstring);

    $ResultSet->EnableRecordCount();
    $ResultSet->Execute();
    $recordCount = $ResultSet->GetRecordCount();

    for ($i=0;$i<$recordCount;$i++)
    {
    $status = $ResultSet->MoveNext();
    $ClearQuestID[$i]= $ResultSet->GetColumnValue(2);
    $State[$i] = $ResultSet->GetColumnValue(3);
    $OwnerEmail[$i] = $ResultSet->GetColumnValue(4);
    $SubmitDate[$i] = $ResultSet->GetColumnValue(5);
    my $SubDate = substr($SubmitDate[$i], 0, 10);
    my $days = 30 * 24 * 60 * 60;
    my ($old_day, $old_month, $old_year) = (localtime(time - $days))[3..5];
    my $cutoffDate = sprintf('%04d-%02d-%02d',$old_year + 1900, $old_month + 1, $old_day);

    if($SubDate lt $cutoffDate)
    {
    $body = "Below Defect is Submitted for more than 30 days.\n\n";
    $body .= "ID -> $ClearQuestID[$i]\n";
    my $mailmsg = CQMailMsg::Build();
    my @SMTPemailsettings = ('SMTP',"<EmailServer>",'do.not.reply@test.com','CQ Admin','1');
    my $setmail_success = $mailmsg->SetMailNotificationSettings(\@SMTPemailsettings);
    my $emailsettings = $mailmsg->GetMailNotificationSettings();
    my @emailsettings = @$emailsettings;
    $mailmsg->AddTo("$OwnerEmail[$i]");
    $mailmsg->SetSubject("Defects submitted for 30 days");
    $mailmsg->SetBody("$body");
    $mailmsg->Deliver();
    CQMailMsg::Unbuild($mailmsg);
    }
    }
    CQPerlExt::CQSession_Unbuild($sessionObj);

    Thanks, we decided not to go with the scheduler outside of CQ. 

    So, I am trying to create a new Email plus rule or email notification with the criteria, attached for your reference.

     Send an auto email reminder to assigned_to users if diff(due_date and TODAY)=20.(reminder 20 days from actual due date) at a fixed state "ERB_Analysis". Earlier i was looking to implement this at a dormant "submitted" state. Now, I modified the scenario a bit to fit into CQ email plus rules. 

    I am stuck here pretty much and want to see if the experts have any suggestions.

    Please advise. Thank You very much!! 

    Attachments

    Updated on 2014-02-25T20:50:47Z at 2014-02-25T20:50:47Z by RajaniMatta
  • pdubovitsky
    pdubovitsky
    376 Posts

    Re: Perl script for Submit Date action notification

    ‏2014-02-25T21:21:49Z  

    Thanks, we decided not to go with the scheduler outside of CQ. 

    So, I am trying to create a new Email plus rule or email notification with the criteria, attached for your reference.

     Send an auto email reminder to assigned_to users if diff(due_date and TODAY)=20.(reminder 20 days from actual due date) at a fixed state "ERB_Analysis". Earlier i was looking to implement this at a dormant "submitted" state. Now, I modified the scenario a bit to fit into CQ email plus rules. 

    I am stuck here pretty much and want to see if the experts have any suggestions.

    Please advise. Thank You very much!! 

    I don't think that Email Plus is capable of sending reminders.
    You can either use suggested external custom script or configure Email Notification package to send reminders according to following instructions
    https://cqadmin.org/wiki/Timed_Notification

    Pavel

  • CQAdmin
    CQAdmin
    103 Posts

    Re: Perl script for Submit Date action notification

    ‏2014-02-26T03:07:29Z  

    Thanks, we decided not to go with the scheduler outside of CQ. 

    So, I am trying to create a new Email plus rule or email notification with the criteria, attached for your reference.

     Send an auto email reminder to assigned_to users if diff(due_date and TODAY)=20.(reminder 20 days from actual due date) at a fixed state "ERB_Analysis". Earlier i was looking to implement this at a dormant "submitted" state. Now, I modified the scenario a bit to fit into CQ email plus rules. 

    I am stuck here pretty much and want to see if the experts have any suggestions.

    Please advise. Thank You very much!! 

    How will you trigger this email plus rule?

  • RajaniMatta
    RajaniMatta
    12 Posts

    Re: Perl script for Submit Date action notification

    ‏2014-02-26T16:19:51Z  
    • CQAdmin
    • ‏2014-02-26T03:07:29Z

    How will you trigger this email plus rule?

    Hi Pavel,

    I am testing this email notification, I do not see any emails sent out.

    Please look at the attachment. Timed notifications don't trigger by actions, they have to meet the condition.

    I have a condition that if diff(due_date and TODAY)=20 then send out email reminder to the assigned_to user. I am testing with an example condition and still does not work. ERB_due_date is not null and ERBDue is tomorrow.

    Please advise.

    $ERB_Due_Date ne '' && DateTimeDiff( $ERB_Due_Date, GetDateTime('+1d') ) < 0

    Attachments

  • pdubovitsky
    pdubovitsky
    376 Posts

    Re: Perl script for Submit Date action notification

    ‏2014-02-26T17:59:46Z  

    Hi Pavel,

    I am testing this email notification, I do not see any emails sent out.

    Please look at the attachment. Timed notifications don't trigger by actions, they have to meet the condition.

    I have a condition that if diff(due_date and TODAY)=20 then send out email reminder to the assigned_to user. I am testing with an example condition and still does not work. ERB_due_date is not null and ERBDue is tomorrow.

    Please advise.

    $ERB_Due_Date ne '' && DateTimeDiff( $ERB_Due_Date, GetDateTime('+1d') ) < 0

    Hi,

    1. What is the pseudo-action name that you use to trigger this notification? It should be the same as parameter used for "TimedNotification.pl", and it should be put in the "Actions" field of the email notification rule.
    http://cqadmin.org/wiki/Timed_Notification#Configure_Scheduler_on_a_ClearQuest_server
    2. Did you enable desirable record type for reminders?
    http://cqadmin.org/wiki/Timed_Notification#Enable_Notification_for_the_record_type
    3. Do you use existing query to filter records or dynamic query criteria?
    http://cqadmin.org/wiki/Timed_Notification#Limit_record_set_for_notification_rules_evaluation
    When configured, you can try to execute "TimedNotification.pl" manually.
     

    Pavel

     

  • RajaniMatta
    RajaniMatta
    12 Posts

    Re: Perl script for Submit Date action notification

    ‏2014-02-26T21:29:33Z  

    Hi,

    1. What is the pseudo-action name that you use to trigger this notification? It should be the same as parameter used for "TimedNotification.pl", and it should be put in the "Actions" field of the email notification rule.
    http://cqadmin.org/wiki/Timed_Notification#Configure_Scheduler_on_a_ClearQuest_server
    2. Did you enable desirable record type for reminders?
    http://cqadmin.org/wiki/Timed_Notification#Enable_Notification_for_the_record_type
    3. Do you use existing query to filter records or dynamic query criteria?
    http://cqadmin.org/wiki/Timed_Notification#Limit_record_set_for_notification_rules_evaluation
    When configured, you can try to execute "TimedNotification.pl" manually.
     

    Pavel

     

    Thanks Pavel, 

    I will give it a try. I did not enable record type and I did not test by running it on the web server #1.

    I really appreciate your assistance in this, will get back to you soon.

     

    Thank You.

    Rajani

     

  • RajaniMatta
    RajaniMatta
    12 Posts

    Re: Perl script for Submit Date action notification

    ‏2014-02-27T11:58:49Z  

    Thanks Pavel, 

    I will give it a try. I did not enable record type and I did not test by running it on the web server #1.

    I really appreciate your assistance in this, will get back to you soon.

     

    Thank You.

    Rajani

     

    Hi Pavel,

    I installed the email notifications package but was not able to find TimedNotifications.pl. 

    Where is this perl script located. I looked in the packages installation directory as well.

     

    Thanks,
    Rajani

     

  • pdubovitsky
    pdubovitsky
    376 Posts

    Re: Perl script for Submit Date action notification

    ‏2014-02-27T12:32:08Z  

    Hi Pavel,

    I installed the email notifications package but was not able to find TimedNotifications.pl. 

    Where is this perl script located. I looked in the packages installation directory as well.

     

    Thanks,
    Rajani

     

    Hi,

    Did you select "Supplementary scripts" option during install? In this case, you can find it in the "script" folder of the installation directory.
    BTW, it would be better to address all package-related questions at the package support site
    http://forum.cqadmin.com/

    Pavel

  • RajaniMatta
    RajaniMatta
    12 Posts

    Re: Perl script for Submit Date action notification

    ‏2014-02-27T13:36:09Z  

    Hi,

    Did you select "Supplementary scripts" option during install? In this case, you can find it in the "script" folder of the installation directory.
    BTW, it would be better to address all package-related questions at the package support site
    http://forum.cqadmin.com/

    Pavel

    Hello,

    I don't remember selecting the supplementary scripts. I just went with default installation selection.

    I will surely address the package issues at this new site. Thanks.

     

    Rajani