You are viewing the PKP Support Forum | PKP Home Wiki

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
What to do if you have a technical problem with OJS:

1. Search the forum. You can do this from the Advanced Search Page or from our Google Custom Search, which will search the entire PKP site. If you are encountering an error, we especially recommend searching the forum for said error.

2. Check the FAQ to see if your question or error has already been resolved.

3. Post a question, but please, only after trying the above two solutions. If it's a workflow or usability question you should probably post to the OJS Editorial Support and Discussion subforum; if you have a development question, try the OJS Development subforum.

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
Posts: 13
Joined: Thu Mar 15, 2007 1:53 pm
Location: Brazil

Return to OJS Technical Support

Who is online

Users browsing this forum: Yahoo [Bot] and 4 guests