postgresql7.1
select
cp.man_id,
cp.course_name,
cp.identifier,
cp.version,
case
when cp.hasmetadata = 't' then 'Yes'
else 'No'
end as hasmetadata,
case
when isscorm = 't' then 'Yes'
else 'No'
end as isscorm,
cp.fs_package_id,
case when fs_package_id is null then 'f'
else 't'
end as lorsm_p,
cp.folder_id,
acs.creation_user,
acs.creation_date,
pf.folder_name,
pf.format_name,
acs.context_id,
case
when cpmc.isenabled = 't' then 'Enabled'
else 'Disabled'
end as isenabled,
case
when cpmc.istrackable = 't' then 'Yes'
else 'No'
end as istrackable,
-- micheles
-- addition for rte stuff
-- 'Click here' as hasrtedata,
case
when
upper(scorm_type) = 'SCO'
then 'Click here'
else ''
end as hasrtedata,
case
when
upper(scorm_type) = 'SCO'
then 'delivery-scorm'
else 'delivery'
end as deliverymethod
from
acs_objects acs,
ims_cp_manifest_class cpmc,
lorsm_course_presentation_fmts pf,
-- micheles
ims_cp_manifests cp left join (select man_id, max(scorm_type) as scorm_type from ims_cp_resources group by man_id ) as cpr using (man_id)
where
cp.man_id = acs.object_id
and
cp.man_id = cpmc.man_id
and
cpmc.community_id = :community_id
and
cp.course_presentation_format = pf.format_id
and
cp.man_id in (select cr.live_revision
from cr_items cr where content_type = 'ims_manifest_object')
order by acs.creation_date desc, cp.man_id asc