As an aside, when I was working on this last year, I got stuck for 1/2 a day trying to make it work. I actually emailed the SQL query to the woman I was working on this for, just to give her an idea of why she had to wait for this fix. Here's what I wrote to her:
Wow, that was painful. I already had the query working to get the submission_type code into the papers report. However, that was when I querying the database directly. When I dropped it into the file that generates the Papers Report, the submission_type code didn't appear - for the last 2 hours! What a nightmare.
Just so you can see what I was dealing with, here's the database query that I was working on:
SELECT p.status AS status,
p.start_time AS start_time,
p.end_time AS end_time,
pp.room_id AS room_id,
p.paper_id AS paper_id,
COALESCE(psl1.setting_value, pspl1.setting_value) AS title,
COALESCE(psl2.setting_value, pspl2.setting_value) AS abstract,
COALESCE(psl3.setting_value, pspl3.setting_value) AS session_type,
COALESCE(tl.setting_value, tpl.setting_value) AS track_title,
p.language AS language
FROM papers p
LEFT JOIN published_papers pp ON (p.paper_id = pp.paper_id)
LEFT JOIN paper_settings pspl1 ON (pspl1.paper_id=p.paper_id AND pspl1.setting_name = 'title' AND pspl1.locale = 'en_US')
LEFT JOIN paper_settings psl1 ON (psl1.paper_id=p.paper_id AND psl1.setting_name = 'title' AND psl1.locale = 'en_US')
LEFT JOIN paper_settings pspl2 ON (pspl2.paper_id=p.paper_id AND pspl2.setting_name = 'abstract' AND pspl2.locale = 'en_US')
LEFT JOIN paper_settings psl2 ON (psl2.paper_id=p.paper_id AND psl2.setting_name = 'abstract' AND psl2.locale = 'en_US')
LEFT JOIN paper_settings pspl3 ON (pspl3.paper_id=p.paper_id AND pspl3.setting_name = 'sessionType')
LEFT JOIN paper_settings psl3 ON (psl3.paper_id=p.paper_id AND psl3.setting_name = 'sessionType')
LEFT JOIN track_settings tpl ON (tpl.track_id=p.track_id AND tpl.setting_name = 'title' AND tpl.locale = 'en_US')
LEFT JOIN track_settings tl ON (tl.track_id=p.track_id AND tl.setting_name = 'title' AND tl.locale = 'en_US')
WHERE p.sched_conf_id = 1
AND submission_progress != 3
ORDER BY title
The problem that took me half the day to find was that this line:
COALESCE(psl3.setting_value, pspl3.setting_value) AS session_type,
couldn't be like this:
COALESCE(psl3.setting_value, pspl3.setting_value) AS sessionType,
I don't think that it liked that I used an uppercase T in the variable name.
Anyway, on with this post...
I have attached the two files that you need to modify (and appended .txt, so that the forum would allow me to upload them).
Also, here's the SQL query you need to use to generate the lookup table so that you can see what submission type the codes correspond to.
- Code: Select all
SELECT controlled_vocab_entries.controlled_vocab_entry_id AS submission_type_id, setting_value AS submission_type
FROM `controlled_vocab_entries`, controlled_vocab_entry_settings
WHERE controlled_vocab_entry_settings.controlled_vocab_entry_id = controlled_vocab_entries.controlled_vocab_entry_id
AND setting_name = 'name'
- SubmissionTypeLookupTable.png (20.66 KiB) Viewed 1094 times
These files also have a few other changes that I made to the report. More informative filename. Doesn't include archived submissions. And maybe a few other changes. These are OCS 2.3.3.1 files.
