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

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
    ACCEPTED ANSWER

    Re: Perl script for Submit Date action notification

    ‏2014-02-11T15:29:43Z  in response to RajaniMatta

    Hello,

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

    Pavel

    • RajaniMatta
      RajaniMatta
      12 Posts
      ACCEPTED ANSWER

      Re: Perl script for Submit Date action notification

      ‏2014-02-11T18:36:52Z  in response to pdubovitsky

      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
        ACCEPTED ANSWER

        Re: Perl script for Submit Date action notification

        ‏2014-02-11T19:36:01Z  in response to RajaniMatta

        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
          ACCEPTED ANSWER

          Re: Perl script for Submit Date action notification

          ‏2014-02-11T20:38:44Z  in response to pdubovitsky
          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
            ACCEPTED ANSWER

            Re: Perl script for Submit Date action notification

            ‏2014-02-11T21:05:54Z  in response to RajaniMatta

            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
              ACCEPTED ANSWER

              Re: Perl script for Submit Date action notification

              ‏2014-02-11T21:47:21Z  in response to pdubovitsky
              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
                ACCEPTED ANSWER

                Re: Perl script for Submit Date action notification

                ‏2014-02-11T21:56:32Z  in response to RajaniMatta

                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

                • RajaniMatta
                  RajaniMatta
                  12 Posts
                  ACCEPTED ANSWER

                  Re: Perl script for Submit Date action notification

                  ‏2014-02-12T17:43:36Z  in response to pdubovitsky

                  Package issues resolved. the user groups were not set.

                  Thanks Pavel.

  • DonaldN
    DonaldN
    204 Posts
    ACCEPTED ANSWER

    Re: Perl script for Submit Date action notification

    ‏2014-02-11T23:57:54Z  in response to RajaniMatta

    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.

    • RajaniMatta
      RajaniMatta
      12 Posts
      ACCEPTED ANSWER

      Re: Perl script for Submit Date action notification

      ‏2014-02-12T18:30:52Z  in response to DonaldN

      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
    99 Posts
    ACCEPTED ANSWER

    Re: Perl script for Submit Date action notification

    ‏2014-02-12T02:52:15Z  in response to RajaniMatta

    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.

  • CQAdmin
    CQAdmin
    99 Posts
    ACCEPTED ANSWER

    Re: Perl script for Submit Date action notification

    ‏2014-02-14T05:32:50Z  in response to RajaniMatta

    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
      ACCEPTED ANSWER

      Re: Perl script for Submit Date action notification

      ‏2014-02-25T20:46:02Z  in response to CQAdmin

      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
        ACCEPTED ANSWER

        Re: Perl script for Submit Date action notification

        ‏2014-02-25T21:21:49Z  in response to RajaniMatta

        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
        99 Posts
        ACCEPTED ANSWER

        Re: Perl script for Submit Date action notification

        ‏2014-02-26T03:07:29Z  in response to RajaniMatta

        How will you trigger this email plus rule?

        • RajaniMatta
          RajaniMatta
          12 Posts
          ACCEPTED ANSWER

          Re: Perl script for Submit Date action notification

          ‏2014-02-26T16:19:51Z  in response to CQAdmin

          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
            ACCEPTED ANSWER

            Re: Perl script for Submit Date action notification

            ‏2014-02-26T17:59:46Z  in response to RajaniMatta

            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
              ACCEPTED ANSWER

              Re: Perl script for Submit Date action notification

              ‏2014-02-26T21:29:33Z  in response to pdubovitsky

              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
                ACCEPTED ANSWER

                Re: Perl script for Submit Date action notification

                ‏2014-02-27T11:58:49Z  in response to RajaniMatta

                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
                  ACCEPTED ANSWER

                  Re: Perl script for Submit Date action notification

                  ‏2014-02-27T12:32:08Z  in response to RajaniMatta

                  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
                    ACCEPTED ANSWER

                    Re: Perl script for Submit Date action notification

                    ‏2014-02-27T13:36:09Z  in response to pdubovitsky

                    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