Advanced reporting and automation with cqperl

The following example cqperl code generates a report.

Perl

# nightlysubmits.pl - A Perl script to list all of the
# defects currently in the submit state.

use CQPerlExt;

# All HCL Compass work is done via a session object. Cqperl
# obtains a session object with the CQSession Build method
# accessible from the CQPerlExt Perl module.
#   API Reference: Session Object->Build

$session = CQSession::Build();

# Once we've obtained the session, we need to logon. This is
# done with the UserLogon method. You need to specify the
# username, the password, and the database name. The fourth
# parameter, dbset, is usually left blank.
#   API Reference: Session object->UserLogon method

$session->UserLogon("admin","","SAMPL","");

# Generating a query involves creation of a QueryDef object.
# This is done via a method of the session object called 
# BuildQuery. It's only parameter is the entitydef 
# (also known as Record Type) that you wish to query on. 
# In this case, we'll use "Defect"
#   API Reference: Session Object->Build Query method

#                  QueryDef Object

#                  EntityDef Object->Name property

$querydef = $session->BuildQuery("Defect");

# The next step (like creating a query through the HCL Compass client) is to decide which fields will be in 
# the Query Result Set. This is done with the BuildField 
# method of the QueryDef object. We'd like to see ID, 
# headline, and submitter.
#   API Reference: QueryDef Object->BuildField method

$querydef->BuildField("id");

$querydef->BuildField("headline");

$querydef->BuildField("submitter");

# Next, we need to build the filters for this query.
# This is done by constructing a tree of FilterOperator
# objects. Creating the top level FilterOperator object for
# any subtree is done with the BuildFilterOperator method
# of the QueryDef object. The BuildFilterOperator method
# takes one parameter, the boolean operator that will
# determine how each of the subtrees behaves. If there is
# only one filter, either AND or OR will work. To specify
# the correct boolean operator, select the proper BoolOp 
# constant and Perl prefix. In this case, we'll use and, so
# therefore, our constant will be $CQPerlExt::CQ_BOOL_OP_AND. 
#   API Reference: QueryDef Object->BuildFilterOperator Method

#                  BoolOp constants
#                  Notation conventions for Perl

$rootfilternode = 
   $querydef->BuildFilterOperator($CQPerlExt::CQ_BOOL_OP_AND);

# Once we have the root FilterOperatorNode, we'll assign a
# filter to it. In this case, state equals submitted. We'll
# use the BuildFilter method of the QueryFilterNode object
# for this. Note that the third parameter to BuildFilter must
# be a Perl reference to an array.
#   API Reference: QueryFilterNode object->BuildFilter method
#                  BoolOp constants
#                  Notation conventions for Perl

@statetest = "Submitted";

$rootfilternode->BuildFilter("State",
                             $CQPerlExt::CQ_COMP_OP_EQ,
                             \@statetest);

# Okay, the Query definition has been created, now it's time 
# to execute it. We go back to the session object for this
# and use the BuildResultSet method. It's only parameter 
# is the QueryDef object we'd previously created. After 
# the result set object is ready, we then execute the query.
#   API Reference: Session object->BuildResultSet method
#                  ResultSet object->Execute method

$resultset = $session->BuildResultSet($querydef);

$resultset->Execute();

# Let's prepare by printing a header for our output.

printf("%13.13s %50.50s %9.9s\n","id","headline","submitter");

printf("%13.13s %50.50s %9.9s\n",

       "-------------",

       "--------------------------------------------------",

       "---------");

# Now, traverse the resultset and print out the output.
# This is done via the MoveNext method of the result set
# object. It will return $CQPerlExt::CQ_SUCCESS as long as
# there are rows to view. GetColumnValue is used to get the
# data from that row of the resultset.
#   API Reference: ResultSet object->MoveNext method
#                  ResultSet object->GetColumnValue method

while ($resultset->MoveNext() == $CQPerlExt::CQ_SUCCESS) {

    printf("%-13.13s %-50.50s %-9.9s\n",

           $resultset->GetColumnValue(1),

           $resultset->GetColumnValue(2),

           $resultset->GetColumnValue(3));
}

# And we're done, so let's release the session

CQSession::Unbuild($session);