Index: openacs-4/packages/lorsm/lib/user-lorsm.xql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/lorsm/lib/user-lorsm.xql,v
diff -u -N -r1.3 -r1.4
--- openacs-4/packages/lorsm/lib/user-lorsm.xql 16 Jan 2008 16:54:44 -0000 1.3
+++ openacs-4/packages/lorsm/lib/user-lorsm.xql 12 Dec 2008 14:25:32 -0000 1.4
@@ -1,88 +1,117 @@
-
-
- select
- cp.man_id,
- cp.course_name,
- cp.identifier,
- cp.version,
- cp.fs_package_id,
- case when cp.fs_package_id is null then 't'
- else 'f'
- end as lorsm_p,
- cp.folder_id,
- acs.creation_user,
- acs.creation_date,
- pf.folder_name,
- pf.format_name,
- acs.context_id,
- cpmc.community_id,
- cpmc.lorsm_instance_id
- from
- ims_cp_manifests cp,
- acs_objects acs,
- ims_cp_manifest_class cpmc,
- lorsm_course_presentation_formats pf
- where
- cp.man_id = acs.object_id
- and
- cp.man_id = cpmc.man_id
- and
- cpmc.lorsm_instance_id = :package
- $extra_query
- and
- cpmc.isenabled = 't'
- and
- pf.format_id = cp.course_presentation_format
- order by acs.creation_date desc
-
-
+
+
+ select
+ cp.man_id, cp.course_name, cp.identifier, cp.version, cp.fs_package_id,
-
-
+ case when cp.fs_package_id is null then 't'
+ else 'f'
+ end as lorsm_p,
+
+ cp.folder_id, acs.creation_user, acs.creation_date, pf.folder_name,
+ pf.format_name, acs.context_id, cpmc.community_id, cpmc.lorsm_instance_id
+ from ims_cp_manifests cp, acs_objects acs, ims_cp_manifest_class cpmc, lorsm_course_presentation_formats pf
+ where cp.man_id = acs.object_id
+ and cp.man_id = cpmc.man_id
+ and cpmc.lorsm_instance_id = :package
+ $extra_query
+ and cpmc.isenabled = 't'
+ and pf.format_id = cp.course_presentation_format
+ order by acs.creation_date desc
+
+
+
+
+
select v.last_viewed
- from views_views v,
- ims_cp_items i,
- ims_cp_organizations o
- where v.viewer_id = :user_id
- and v.object_id = i.ims_item_id
- and i.org_id = o.org_id
- and o.man_id = :man_id
+ from views_views v, ims_cp_items i, ims_cp_organizations o
+ where v.viewer_id = :user_id
+ and v.object_id = i.ims_item_id
+ and i.org_id = o.org_id
+ and o.man_id = :man_id
order by v.last_viewed desc
limit 1
-
-
+
+
-
-
+
+
select i.ims_item_id
- from ims_cp_items i,
- ims_cp_organizations o
- where o.man_id = :man_id
- and i.org_id = o.org_id
-
-
+ from ims_cp_items i, ims_cp_organizations o
+ where o.man_id = :man_id
+ and i.org_id = o.org_id
+
+
-
-
+
+
select v.object_id
- from views_views v
- where v.viewer_id = :user_id
- and v.object_id in ([join $all_items ,])
-
-
+ from views_views v
+ where v.viewer_id = :user_id
+ and v.object_id in ([join $all_items ,])
+
+
-
-
- select
- item_id
- from
- cr_revisions
- where
- revision_id = :man_id
-
-
+
+
+ select item_id
+ from cr_revisions
+ where revision_id = :man_id
+
+
-
\ No newline at end of file
+
+
+ select cp.man_id,
+ case when upper(scorm_type) = 'SCO' then 'delivery-scorm'
+ else 'delivery'
+ end as needscorte
+
+ from 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 = :man_id
+
+
+
+
+
+ select
+ cp.course_name,
+ cp.fs_package_id,
+ isscorm,
+ pf.folder_name,
+ pf.format_name,
+ case when upper(scorm_type) = 'SCO' then 'delivery-scorm'
+ else 'delivery'
+ end as deliverymethod
+
+ from 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), lorsm_course_presentation_formats pf
+ where cp.man_id = :man_id
+ and cp.parent_man_id = 0
+ and cp.course_presentation_format = pf.format_id
+
+
+
+
+
+ select lorsm.track_id as track_id, cmi.lesson_status as lesson_status
+ from lorsm_student_track lorsm, lorsm_cmi_core cmi
+ where lorsm.user_id = $user_id
+ and lorsm.community_id = $community_id
+ and lorsm.course_id = $man_id
+ and lorsm.track_id = cmi.track_id
+ and cmi.man_id = $man_id
+ and cmi.item_id = $man_id
+ order by lorsm.track_id desc
+ limit 1
+
+
+
+