Forum Discussion

mfrensko's avatar
mfrensko
New Contributor
5 years ago
Solved

findReviews method referenced in JSON API

I’m trying to use Code Collaborator’s JSON API (collab-common 11.5.11503 API) to find reviews by searching for a particular field, however I’m having trouble understanding how to use the findReviews ...
API
json
jsonapi
  • Fyodor_A's avatar
    Fyodor_A
    5 years ago

    The 'findReviews' method has two arguments: 'findPlace' and 'searchText'.
    'findPlace' accepts the following values:
    0 for searching Everywhere
    1 for searching in Reviews Titles
    2 for searching in User Comments
    3 for searching in Custom Fields

    Please refer to the following example of the JSON query:
    {"command":"ReviewService.findReviews","args":{"findPlace":1,"searchText":"someword"}}
    This will return the list of reviews the titles of which contains "someword".

    I recommend that you use the command-line client command instead of the JSON API query.
    ccollab admin find review
    This command returns the list of reviews found by the specified criteria.

     

    As for searching by the "Completed On" value, I don't see a way to this using neither the command-line client nor the JSON API request. However, you can do it with the configured 'Recently Completed Reviews' report.

    Custom reports are also can be gathered by an SQL query. For example, the following query will return the list of the reviews completed in a period from 2019-08-07 to 2019-10-15:

    SELECT
    r.review_id AS `reviewid`

    FROM
    review r
    LEFT OUTER JOIN ( SELECT a.activity_reviewid,MIN(a.activity_startsecs) startsecs,MAX(a.activity_startsecs + a.activity_durationsecs) endsecs,SUM(a.activity_durationsecs) totalsecs,SUM(IF((a.activity_code)=('R'),a.activity_durationsecs,0)) reviewersecs,SUM(IF((a.activity_code)=('A'),a.activity_durationsecs,0)) authorsecs FROM activity a GROUP BY a.activity_reviewid) act_summary ON (act_summary.activity_reviewid = r.review_id)
    LEFT OUTER JOIN ( SELECT d.defect_reviewid,COUNT(defect_id) numDefects,SUM(IF((d.defect_state)=('O'),1,0)) numOpenDefects FROM defect d GROUP BY d.defect_reviewid) defect_summary ON (defect_summary.defect_reviewid = r.review_id)
    LEFT OUTER JOIN (SELECT a.assignment_reviewid revid, IF((COUNT(u.user_id)= 1),MIN(u.user_name),'(multiple)') user_name, IF((COUNT(u.user_id)= 1),MIN(u.user_login),'(multiple)') user_login FROM assignment a INNER JOIN user u ON (a.assignment_userid=u.user_id) WHERE 2=a.assignment_roleid % 4 GROUP BY a.assignment_reviewid) rAuthor ON (rAuthor.revid = r.review_id)
    WHERE
    (IF((r.review_phaseid)=(5),r.review_lastphasechangedateinsecs,NULL)>=1565125200 AND IF((r.review_phaseid)=(5),r.review_lastphasechangedateinsecs,NULL)<=1571086800)
    AND (r.review_phaseid=5)
    ORDER BY
    r.review_id DESC

Fyodor_A's avatar
Fyodor_A
SmartBear Alumni (Retired)
5 years ago

The 'findReviews' method has two arguments: 'findPlace' and 'searchText'.
'findPlace' accepts the following values:
0 for searching Everywhere
1 for searching in Reviews Titles
2 for searching in User Comments
3 for searching in Custom Fields

Please refer to the following example of the JSON query:
{"command":"ReviewService.findReviews","args":{"findPlace":1,"searchText":"someword"}}
This will return the list of reviews the titles of which contains "someword".

I recommend that you use the command-line client command instead of the JSON API query.
ccollab admin find review
This command returns the list of reviews found by the specified criteria.

 

As for searching by the "Completed On" value, I don't see a way to this using neither the command-line client nor the JSON API request. However, you can do it with the configured 'Recently Completed Reviews' report.

Custom reports are also can be gathered by an SQL query. For example, the following query will return the list of the reviews completed in a period from 2019-08-07 to 2019-10-15:

SELECT
r.review_id AS `reviewid`

FROM
review r
LEFT OUTER JOIN ( SELECT a.activity_reviewid,MIN(a.activity_startsecs) startsecs,MAX(a.activity_startsecs + a.activity_durationsecs) endsecs,SUM(a.activity_durationsecs) totalsecs,SUM(IF((a.activity_code)=('R'),a.activity_durationsecs,0)) reviewersecs,SUM(IF((a.activity_code)=('A'),a.activity_durationsecs,0)) authorsecs FROM activity a GROUP BY a.activity_reviewid) act_summary ON (act_summary.activity_reviewid = r.review_id)
LEFT OUTER JOIN ( SELECT d.defect_reviewid,COUNT(defect_id) numDefects,SUM(IF((d.defect_state)=('O'),1,0)) numOpenDefects FROM defect d GROUP BY d.defect_reviewid) defect_summary ON (defect_summary.defect_reviewid = r.review_id)
LEFT OUTER JOIN (SELECT a.assignment_reviewid revid, IF((COUNT(u.user_id)= 1),MIN(u.user_name),'(multiple)') user_name, IF((COUNT(u.user_id)= 1),MIN(u.user_login),'(multiple)') user_login FROM assignment a INNER JOIN user u ON (a.assignment_userid=u.user_id) WHERE 2=a.assignment_roleid % 4 GROUP BY a.assignment_reviewid) rAuthor ON (rAuthor.revid = r.review_id)
WHERE
(IF((r.review_phaseid)=(5),r.review_lastphasechangedateinsecs,NULL)>=1565125200 AND IF((r.review_phaseid)=(5),r.review_lastphasechangedateinsecs,NULL)<=1571086800)
AND (r.review_phaseid=5)
ORDER BY
r.review_id DESC