SQL to show the proceeding of editorial process

Are you responsible for making OJS work -- installing, upgrading, migrating or troubleshooting? Do you think you've found a bug? Post in this forum.

Moderators: jmacgreg, btbell, michael, bdgregg, barbarah, asmecher

Forum rules
The Public Knowledge Project Support Forum is moving to http://forum.pkp.sfu.ca

This forum will be maintained permanently as an archived historical resource, but all new questions should be added to the new forum. Questions will no longer be monitored on this old forum after March 30, 2015.
Posts: 13
Joined: Thu Mar 15, 2007 1:53 pm
Location: Brazil

SQL to show the proceeding of editorial process

Postby mcampos » Thu Jun 14, 2007 12:55 pm

I needed to make a report to show the date that each phase of editorial process was initiated and finished. So, I show the SQL instruction to obtain theses informations. I don't put all phases because my system don't use all. This can be usefull to another developer.

SELECT a.article_id, a.title, concat(author.first_name, ' ', author.last_name) as Author,
a.date_submitted ,
concat(reviewer.first_name, ' ', reviewer.last_name) as reviewer,
r.date_notified as date_reviewer_notified,
r.date_completed as date_reviewer_completed,
concat(copyeditor.first_name, ' ', copyeditor.last_name) as copyeditor,
cp.date_notified as date_copyEditor_notified ,
cp.date_completed as date_copyEditor_completed,
cp.date_final_notified as date_copyEditor_final_notified,
cp.date_final_completed as date_copyEditor_final_completed,
concat(layoutEditor.first_name, ' ', layoutEditor.last_name) as layoutEditor,
l.date_notified as date_layout_notified,
l.date_completed as date_layout_completed
FROM articles a
left outer join copyed_assignments cp
on cp.article_id = a.article_id
left outer join users author on author.user_id = a.user_id
left outer join review_assignments r
on r.article_id = a.article_id
left outer join users reviewer on reviewer.user_id = r.reviewer_id
left outer join users copyeditor on copyeditor.user_id = cp.copyeditor_id
left outer join layouted_assignments l on l.article_id = a.article_id
left outer join users layoutEditor on layoutEditor.user_id = l.editor_id
order by a.article_id

Return to “OJS Technical Support”

Who is online

Users browsing this forum: Google [Bot] and 2 guests