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 method in the API.
The goal is to search a certain field for a particular string in order to find reviews. For example:
{"command":"ReviewService.findReviews",
"args":{"searchText":"Updater", "customFieldName":"title"}}
My understanding is that this command should search for reviews containing “Updater” in their title. However, when running this command, the reviews returned contain “Updater” in fields not just limited to the title. I can’t find examples or support on how to use this function properly.
Also, once I can get findReviews to return reviews that fit more limited search criteria, I wanted to know if I could use fields that are not returned in the results as search criteria. For example, “Completed On” is an available field in a review, but it isn’t a field returned in the find request.
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
