Index: openacs-4/packages/imsld/tcl/imsld-cp-procs.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/imsld/tcl/imsld-cp-procs.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/imsld/tcl/imsld-cp-procs.xql 1 Feb 2006 11:34:48 -0000 1.1 @@ -0,0 +1,13 @@ + + + + + select icr.manifest_id + from imsld_cp_resources icr, cr_items cri + where icr.resource_id = cri.live_revision + and cri.item_id = :resource_id + + + + + Index: openacs-4/packages/imsld/tcl/imsld-cr-procs.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/imsld/tcl/imsld-cr-procs.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/imsld/tcl/imsld-cr-procs.xql 1 Feb 2006 11:34:48 -0000 1.1 @@ -0,0 +1,14 @@ + + + + + + + select 1 + from imsld_cp_files icf, cr_items cri + where cri.item_id = :item_id + and cri.live_revision = icf.imsld_file_id + + + + Index: openacs-4/packages/imsld/tcl/imsld-fs-procs.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/imsld/tcl/imsld-fs-procs.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/imsld/tcl/imsld-fs-procs.xql 1 Feb 2006 11:34:48 -0000 1.1 @@ -0,0 +1,24 @@ + + + + + + + update cr_revisions + set content = :filename, + mime_type = :mime_type, + content_length = :content_length + where revision_id = :revision_id + + + + + + update cr_revisions + set content_length = :content_length + where revision_id = :revision_id + + + + + Index: openacs-4/packages/imsld/tcl/imsld-parse-procs.tcl =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/imsld/tcl/imsld-parse-procs.tcl,v diff -u -r1.27 -r1.28 --- openacs-4/packages/imsld/tcl/imsld-parse-procs.tcl 30 Jan 2006 19:31:05 -0000 1.27 +++ openacs-4/packages/imsld/tcl/imsld-parse-procs.tcl 1 Feb 2006 11:34:48 -0000 1.28 @@ -1545,7 +1545,7 @@ set role_ref [$email_data child all imsld:role-ref] imsld::parse::validate_multiplicity -tree $role_ref -multiplicity 1 -element_name role-ref(email-data) -equal set ref [string tolower [imsld::parse::get_attribute -node $role_ref -attr_name ref]] - if { ![db_0or1row get_role_id { + if { ![db_0or1row get_role_id_from_ref { select ir.item_id as role_id from imsld_rolesi ir where ir.identifier = :ref @@ -1612,14 +1612,16 @@ if { [llength $manager] } { imsld::parse::validate_multiplicity -tree $manager -multiplicity 1 -element_name conference-manager -equal set role_ref [string tolower [imsld::parse::get_attribute -node $manager -attr_name role-ref]] - if { ![db_0or1row get_role_id { + if { ![db_0or1row get_role_id_from_role_ref { select item_id as manager_id from imsld_rolesi where identifier = :role_ref and content_revision__is_live(role_id) = 't' and component_id = :component_id }] } { # there is no role with that identifier, return the error return [list 0 "[_ imsld.lt_There_is_no_role_with_1]"] + } else { + set manager_id $the_item_id } } @@ -1691,7 +1693,7 @@ imsld::parse::validate_multiplicity -tree $participant_list -multiplicity 1 -element_name conference-participant -greather_than foreach participant $participant_list { set role_ref [string tolower [imsld::parse::get_attribute -node $participant -attr_name role-ref]] - if { ![db_0or1row get_role_id { + if { ![db_0or1row get_role_id_from_role_ref { select item_id as participant_id from imsld_rolesi where identifier = :role_ref @@ -1700,6 +1702,8 @@ }] } { # there is no role with that identifier, return the error return [list 0 "[_ imsld.lt_There_is_no_role_with_2]"] + } else { + set participant_id $the_item_id } # map conference with participant role relation_add imsld_conf_part_rel $conference_id $participant_id @@ -1710,7 +1714,7 @@ if { [llength $observer_list] } { foreach observer $observer_list { set role_ref [string tolower [imsld::parse::get_attribute -node $observer -attr_name role-ref]] - if { ![db_0or1row get_role_id { + if { ![db_0or1row get_role_id_from_role_ref { select item_id as observer_id from imsld_rolesi where identifier = :role_ref @@ -1719,6 +1723,8 @@ }] } { # there is no role with that identifier, return the error return [list 0 "[_ imsld.lt_There_is_no_role_with_3]"] + } else { + set observer_id $the_item_id } # map conference with observer role relation_add imsld_conf_obser_rel $conference_id $observer_id @@ -1730,7 +1736,7 @@ if { [llength $moderator_list] } { foreach moderator $moderator_list { set role_ref [string tolower [imsld::parse::get_attribute -node $moderator -attr_name role-ref]] - if { ![db_0or1row get_role_id { + if { ![db_0or1row get_role_id_from_role_ref { select item_id as moderator_id from imsld_rolesi where identifier = :role_ref @@ -1739,6 +1745,8 @@ }] } { # there is no role with that identifier, return the error return [list 0 "[_ imsld.lt_There_is_no_role_with_4]"] + } else { + set moderator_id $the_item_list } # map conference with moderator role relation_add imsld_conf_moder_rel $conference_id $moderator_id @@ -2573,6 +2581,7 @@ and component_id = :component_id }] } { # ok, last try: searching in the rest of activity structures... + set searching_ref $learning_activity_ref if { [db_0or1row get_struct_id { select item_id as refrenced_struct_id, structure_id @@ -2586,7 +2595,7 @@ # do the mappings relation_add imsld_as_as_rel $activity_structure_id $refrenced_struct_id # store the order - db_dml update_activity_structure { + db_dml update_activity_structure_from_structure_id { update imsld_activity_structures set sort_order = :sort_order where structure_id = :structure_id @@ -2627,7 +2636,7 @@ # finally, do the mappings relation_add imsld_as_as_rel $activity_structure_id $activity_structure_ref_id # store the order - db_dml update_activity_structure { + db_dml update_activity_structure_from_activity_structure_ref_id { update imsld_activity_structures set sort_order = :sort_order where structure_id = (select live_revision from cr_items where item_id = :activity_structure_ref_id) @@ -2688,6 +2697,7 @@ and component_id = :component_id }] } { # ok, last try: searching in the rest of activity structures... + set searching_ref $support_activity_ref if { [db_0or1row get_struct_id { select item_id as refrenced_struct_id, structure_id @@ -2701,7 +2711,7 @@ # do the mappings relation_add imsld_as_as_rel $activity_structure_id $refrenced_struct_id # store the order - db_dml update_activity_structure { + db_dml update_activity_structure_from_structure_id { update imsld_activity_structures set sort_order = :sort_order where structure_id = :structure_id @@ -2742,7 +2752,7 @@ # finally, do the mappings relation_add imsld_as_as_rel $activity_structure_id $activity_structure_ref_id # store the order - db_dml update_activity_structure { + db_dml update_activity_structure_from_activity_structure_ref_id { update imsld_activity_structures set sort_order = :sort_order where structure_id = (select live_revision from cr_items where item_id = :activity_structure_ref_id) @@ -2785,6 +2795,7 @@ # 1. the referenced activity structure has already been created: get the id from the database and do the mappings # 2. the referenced activity structure hasn't been created: invoke the parse_and_create_activity_structure proc, # but first verify that the activity structure exists in the manifest + set searching_ref $ref if { [db_0or1row get_struct_id { select item_id as refrenced_struct_id, structure_id @@ -2796,7 +2807,7 @@ # case one, just do the mappings relation_add imsld_as_as_rel $activity_structure_id $refrenced_struct_id # store the order - db_dml update_activity_structure { + db_dml update_activity_structure_from_structure_id { update imsld_activity_structures set sort_order = :sort_order where structure_id = :structure_id @@ -2835,7 +2846,7 @@ # finally, do the mappings relation_add imsld_as_as_rel $activity_structure_id $activity_structure_ref_id # store the order - db_dml update_activity_structure { + db_dml update_activity_structure_from_activity_structure_ref_id { update imsld_activity_structures set sort_order = :sort_order where structure_id = (select live_revision from cr_items where item_id = :activity_structure_ref_id) Index: openacs-4/packages/imsld/tcl/imsld-parse-procs.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/imsld/tcl/imsld-parse-procs.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/imsld/tcl/imsld-parse-procs.xql 1 Feb 2006 11:34:48 -0000 1.1 @@ -0,0 +1,402 @@ + + + + + + + + select item_id as resource_id + from imsld_cp_resourcesi + where identifier = :resource_identifier + and manifest_id = :manifest_id + + + + + + + + select env.component_id + from imsld_environmentsi env + where content_revision__is_live(env.environment_id) = 't' + and env.item_id = :environment_id + + + + + + + + select ir.item_id as role_id + from imsld_rolesi ir + where ir.identifier = :ref + and content_revision__is_live(ir.role_id) = 't' + and ir.component_id = :component_id + + + + + + + + select item_id as the_item_id + from imsld_rolesi + where identifier = :role_ref + and content_revision__is_live(role_id) = 't' + and component_id = :component_id + + + + + + + select item_id as refrenced_env_id + from imsld_environmentsi + where identifier = :ref + and content_revision__is_live(environment_id) = 't' + and component_id = :component_id + + + + + + + + select item_id as environment_id + from imsld_environmentsi + where identifier = :environment_ref + and content_revision__is_live(environment_id) = 't' and + component_id = :component_id + + + + + + + + select item_id as role_id + from imsld_rolesi + where identifier = :ref + and content_revision__is_live(role_id) = 't' + and component_id = :component_id + + + + + + + + select item_id as environment_id + from imsld_environmentsi + where identifier = :environment_ref + and content_revision__is_live(environment_id) = 't' + and component_id = :component_id + + + + + + + + select item_id as environment_id + from imsld_environmentsi + where identifier = :environment_ref + and content_revision__is_live(environment_id) = 't' + and component_id = :component_id + + + + + + + + select item_id as activity_id, + activity_id as learning_activity_id + from imsld_learning_activitiesi + where identifier = :learning_activity_ref + and content_revision__is_live(activity_id) = 't' + and component_id = :component_id + + + + + + + + select item_id as activity_id, + activity_id as support_activity_id + from imsld_support_activitiesi + where identifier = :learning_activity_ref + and content_revision__is_live(activity_id) = 't' + and component_id = :component_id + + + + + + + + select item_id as refrenced_struct_id, + structure_id + from imsld_activity_structuresi + where identifier = :searching_ref + and content_revision__is_live(structure_id) = 't' + and component_id = :component_id + + + + + + + + update imsld_activity_structures + set sort_order = :sort_order + where structure_id = :structure_id + + + + + + + + update imsld_activity_structures + set sort_order = :sort_order + where structure_id = (select live_revision from cr_items where item_id = :activity_structure_ref_id) + + + + + + + + update imsld_support_activities + set sort_order = :sort_order + where activity_id = :support_activity_id + + + + + + + + update imsld_learning_activities + set sort_order = :sort_order + where activity_id = :learning_activity_id + + + + + + + + select item_id as activity_id, + activity_id as support_activity_id + from imsld_support_activitiesi + where identifier = :support_activity_ref + and content_revision__is_live(activity_id) ='t' + and component_id = :component_id + + + + + + + + select item_id as activity_id, + activity_id as learning_activity_id + from imsld_learning_activitiesi + where identifier = :support_activity_ref + and content_revision__is_live(activity_id) = 't' + and component_id = :component_id + + + + + + + + + + + select cr4.item_id as component_id + from imsld_components ic, imsld_methods im, imsld_plays ip, imsld_acts ia, + cr_revisions cr0, cr_revisions cr1, cr_revisions cr2, cr_revisions cr3, cr_revisions cr4 + where cr4.revision_id = ic.component_id + and content_revision__is_live(ic.component_id) = 't' + and ic.imsld_id = cr3.item_id + and content_revision__is_live(cr3.revision_id) = 't' + and cr3.item_id = im.imsld_id + and im.method_id = cr2.revision_id + and cr2.item_id = ip.method_id + and ip.play_id = cr1.revision_id + and cr1.item_id = ia.play_id + and ia.act_id = cr0.revision_id + and cr0.item_id = :act_id + + + + + + + + select ir.item_id as role_id + from imsld_rolesi ir + where ir.identifier = :role_ref_ref + and content_revision__is_live(ir.role_id) = 't' + and ir.component_id = :component_id + + + + + + + select la.item_id as learning_activity_id + from imsld_learning_activitiesi la + where la.identifier = :learning_activity_ref_ref + and content_revision__is_live(la.activity_id) = 't' + and la.component_id = :component_id + + + + + + + + select item_id as support_activity_id + from imsld_support_activitiesi + where identifier = :learning_activity_ref_ref + and content_revision__is_live(activity_id) = 't' + and component_id = :component_id + + + + + + + + select item_id as activity_structure_id + from imsld_activity_structuresi + where identifier = :learning_activity_ref_ref + and content_revision__is_live(structure_id) = 't' + and component_id = :component_id + + + + + + + + select sa.item_id as support_activity_id + from imsld_support_activitiesi sa + where sa.identifier = :support_activity_ref_ref + and content_revision__is_live(sa.activity_id) = 't' + and sa.component_id = :component_id + + + + + + + + select item_id as learning_activity_id + from imsld_learning_activitiesi + where identifier = :support_activity_ref_ref + and content_revision__is_live(activity_id) = 't' + and component_id = :component_id + + + + + + + + select item_id as activity_structure_id + from imsld_activity_structuresi + where identifier = :support_activity_ref_ref + and content_revision__is_live(structure_id) = 't' + and component_id = :component_id + + + + + + + + select ias.item_id as activity_structure_id + from imsld_activity_structuresi ias + where ias.identifier = :activity_structure_ref_ref + and content_revision__is_live(ias.structure_id) = 't' + and ias.component_id = :component_id + + + + + + + + select item_id as learning_activity_id + from imsld_learning_activitiesi + where identifier = :activity_structure_ref_ref + and content_revision__is_live(activity_id) = 't' + and component_id = :component_id + + + + + + + + select item_id as support_activity_id + from imsld_support_activitiesi + where identifier = :activity_structure_ref_ref + and content_revision__is_live(activity_id) = 't' + and component_id = :component_id + + + + + + + + select env.item_id as environment_id + from imsld_environmentsi env + where env.identifier = :environment_ref_ref + and content_revision__is_live(env.environment_id) = 't' + and env.component_id = :component_id + + + + + + + + select item_id as role_part_id + from imsld_role_partsi + where identifier = :ref + and content_revision__is_live(role_part_id) = 't' + and act_id = :act_id + + + + + + + + select item_id as play_id + from imsld_playsi + where identifier = :ref + and content_revision__is_live(play_id) = 't' + and method_id = :method_id + + + + + + + + select 1 from imsld_cp_resources where identifier = :resource_identifier and manifest_id = :manifest_id + + + + + Index: openacs-4/packages/imsld/tcl/imsld-procs.tcl =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/imsld/tcl/imsld-procs.tcl,v diff -u -r1.19 -r1.20 --- openacs-4/packages/imsld/tcl/imsld-procs.tcl 30 Jan 2006 19:31:05 -0000 1.19 +++ openacs-4/packages/imsld/tcl/imsld-procs.tcl 1 Feb 2006 11:34:48 -0000 1.20 @@ -906,7 +906,7 @@ where item_id = :object_id_two and content_revision__is_live(activity_id) = 't' } - if { ![db_string completed_p { + if { ![db_string completed_p_from_la { select count(*) from imsld_status_user where completed_id = :learning_activity_id @@ -926,7 +926,7 @@ where item_id = :object_id_two and content_revision__is_live(activity_id) = 't' } - if { ![db_string completed_p { + if { ![db_string completed_p_from_sa { select count(*) from imsld_status_user where completed_id = :support_activity_id @@ -1014,7 +1014,7 @@ } switch $type { learning { - if { [db_string completed { + if { [db_string completed_from_la { select count(*) from imsld_status_user where completed_id = content_item__get_live_revision(:learning_activity_id) and user_id = :user_id @@ -1023,7 +1023,7 @@ } } support { - if { [db_string completed { + if { [db_string completed_from_sa { select count(*) from imsld_status_user where completed_id = content_item__get_live_revision(:support_activity_id) and user_id = :user_id @@ -1032,7 +1032,7 @@ } } structure { - if { [db_string completed { + if { [db_string completed_from_as { select count(*) from imsld_status_user where completed_id = content_item__get_live_revision(:activity_structure_id) and user_id = :user_id @@ -1311,14 +1311,14 @@ set learning_objective_item_id "" if { ![string eq "" $imsld_item_id] } { - db_0or1row get_lo_id { + db_0or1row get_lo_id_from_iii { select learning_objective_id as learning_objective_item_id from imsld_imsldsi where item_id = :imsld_item_id and content_revision__is_live(imsld_id) = 't' } } elseif { ![string eq "" $activity_item_id] } { - db_0or1row get_lo_id { + db_0or1row get_lo_id_from_aii { select learning_objective_id as learning_objective_item_id from imsld_learning_activitiesi where item_id = :activity_item_id @@ -1404,14 +1404,14 @@ set prerequisite_item_id "" if { ![string eq "" $imsld_item_id] } { - db_0or1row get_lo_id { + db_0or1row get_lo_id_from_iii { select prerequisite_id as prerequisite_item_id from imsld_imsldsi where item_id = :imsld_item_id and content_revision__is_live(imsld_id) = 't' } } elseif { ![string eq "" $activity_item_id] } { - db_0or1row get_lo_id { + db_0or1row get_lo_id_from_aii { select prerequisite_id as prerequisite_item_id from imsld_learning_activitiesi where item_id = :activity_item_id @@ -2005,7 +2005,7 @@ finished } support { - db_1row get_support_activity_info { + db_1row get_support_activity_info_from_isa { select coalesce(title,identifier) as activity_title, item_id as activity_item_id from imsld_support_activitiesi @@ -2035,7 +2035,7 @@ finished } structure { - db_1row get_support_activity_info { + db_1row get_support_activity_info_from_ias { select coalesce(title,identifier) as activity_title, item_id as structure_item_id from imsld_activity_structuresi @@ -2461,8 +2461,8 @@ #if not done yet, tag the resource as finished - if {![db_0or1row check_completed_resource { - select 1 + if {![db_string check_completed_resource { + select count(*) from imsld_status_user where completed_id=:resource_id }] } { Index: openacs-4/packages/imsld/tcl/imsld-procs.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/imsld/tcl/imsld-procs.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/imsld/tcl/imsld-procs.xql 1 Feb 2006 11:34:48 -0000 1.1 @@ -0,0 +1,1721 @@ + + + + + + + + select dc.community_id + from imsld_cp_manifestsi im, acs_objects ao, dotlrn_communities dc + where im.object_package_id = ao.package_id + and ao.context_id = dc.package_id + and im.manifest_id = :manifest_id + + + + + + + + select icm.manifest_id, + ii.imsld_id, + im.method_id, + tl.time_in_seconds, + icm.creation_date + from imsld_cp_manifestsi icm, imsld_cp_organizationsi ico, + imsld_imsldsi ii, imsld_methodsi im, imsld_time_limitsi tl + where im.imsld_id = ii.item_id + and ii.organization_id = ico.item_id + and ico.manifest_id = icm.item_id + and im.time_limit_id is not null + and im.time_limit_id = tl.item_id + and content_revision__is_live(im.method_id) = 't' + + + + + + + + select 1 + where (extract(epoch from now()) - extract(epoch from timestamp :creation_date) - :time_in_seconds > 0) + + + + + + + + select app.user_id + from dotlrn_member_rels_approved app + where app.community_id = :community_id + and app.member_state = 'approved' + + + + + + + + select icm.manifest_id, + ii.imsld_id, + ip.play_id, + tl.time_in_seconds, + icm.creation_date + from imsld_cp_manifestsi icm, imsld_cp_organizationsi ico, + imsld_imsldsi ii, imsld_methodsi im, imsld_plays ip, + imsld_time_limitsi tl + where ip.method_id = im.item_id + and im.imsld_id = ii.item_id + and ii.organization_id = ico.item_id + and ico.manifest_id = icm.item_id + and ip.time_limit_id is not null + and ip.time_limit_id = tl.item_id + and content_revision__is_live(ip.play_id) = 't' + + + + + + + + + + + select icm.manifest_id, + ii.imsld_id, + ip.play_id, + ia.act_id, + tl.time_in_seconds, + icm.creation_date + from imsld_cp_manifestsi icm, imsld_cp_organizationsi ico, + imsld_imsldsi ii, imsld_methodsi im, imsld_playsi ip, imsld_acts ia, + imsld_time_limitsi tl + where ia.play_id = ip.item_id + and ip.method_id = im.item_id + and im.imsld_id = ii.item_id + and ii.organization_id = ico.item_id + and ico.manifest_id = icm.item_id + and ia.time_limit_id is not null + and ia.time_limit_id = tl.item_id + and content_revision__is_live(ia.act_id) = 't' + + + + + + + + + + + select icm.manifest_id, + sa.activity_id, + ii.imsld_id, + ip.play_id, + ia.act_id, + irp.role_part_id, + tl.time_in_seconds, + icm.creation_date + from imsld_cp_manifestsi icm, imsld_cp_organizationsi ico, + imsld_imsldsi ii, imsld_methodsi im, imsld_playsi ip, + imsld_actsi ia, imsld_role_partsi irp, imsld_support_activitiesi sa, + imsld_time_limitsi tl + where sa.item_id = irp.support_activity_id + and irp.act_id = ia.item_id + and ia.play_id = ip.item_id + and ip.method_id = im.item_id + and im.imsld_id = ii.item_id + and ii.organization_id = ico.item_id + and ico.manifest_id = icm.item_id + and sa.time_limit_id is not null + and sa.time_limit_id = tl.item_id + and content_revision__is_live(sa.activity_id) = 't' + + + + + + + + + + + select icm.manifest_id, + la.activity_id, + ii.imsld_id, + ip.play_id, + ia.act_id, + irp.role_part_id, + tl.time_in_seconds, + icm.creation_date + from imsld_cp_manifestsi icm, imsld_cp_organizationsi ico, + imsld_imsldsi ii, imsld_methodsi im, imsld_playsi ip, + imsld_actsi ia, imsld_role_partsi irp, imsld_learning_activitiesi la, + imsld_time_limitsi tl + where la.item_id = irp.learning_activity_id + and irp.act_id = ia.item_id + and ia.play_id = ip.item_id + and ip.method_id = im.item_id + and im.imsld_id = ii.item_id + and ii.organization_id = ico.item_id + and ico.manifest_id = icm.item_id + and la.time_limit_id is not null + and la.time_limit_id = tl.item_id + and content_revision__is_live(la.activity_id) = 't' + + + + + + + + + + + select item_id as role_part_item_id + from imsld_role_partsi + where role_part_id = :role_part_id + + + + + + + + insert into imsld_status_user (imsld_id, + play_id, + act_id, + completed_id, + user_id, + type, + finished_date) + ( + select :imsld_id, + :play_id, + :act_id, + :role_part_id, + :user_id, + 'act', + now() + where not exists (select 1 from imsld_status_user where imsld_id = :imsld_id and user_id = :user_id and completed_id = :role_part_id) + ) + + + + + + + + select case + when learning_activity_id is not null + then 'learning' + when support_activity_id is not null + then 'support' + when activity_structure_id is not null + then 'structure' + else 'none' + end as type, + content_item__get_live_revision(coalesce(learning_activity_id,support_activity_id,activity_structure_id)) as activity_id + from imsld_role_parts + where role_part_id = :role_part_id + + + + + + + + select item_id as act_item_id + from imsld_actsi + where act_id = :act_id + + + + + + + + insert into imsld_status_user (imsld_id, + play_id, + completed_id, + user_id, + type, + finished_date) + ( + select :imsld_id, + :play_id, + :act_id, + :user_id, + 'act', + now() + where not exists (select 1 from imsld_status_user where imsld_id = :imsld_id and user_id = :user_id and completed_id = :act_id) + ) + + + + + + + + select rp.role_part_id + from imsld_role_parts rp, imsld_actsi ia + where rp.act_id = ia.item_id + and ia.act_id = :act_id + and content_revision__is_live(rp.role_part_id) = 't' + + + + + + + + insert into imsld_status_user (imsld_id, + completed_id, + user_id, + type, + finished_date) + ( + select :imsld_id, + :play_id, + :user_id, + 'play', + now() + where not exists (select 1 from imsld_status_user where imsld_id = :imsld_id and user_id = :user_id and completed_id = :play_id) + ) + + + + + + + + select ia.act_id + from imsld_acts ia, imsld_playsi ip + where ia.play_id = ip.item_id + and ip.play_id = :play_id + and content_revision__is_live(ia.act_id) = 't' + + + + + + + + insert into imsld_status_user (imsld_id, + completed_id, + user_id, + type, + finished_date) + ( + select :imsld_id, + :imsld_id, + :user_id, + 'play', + now() + where not exists (select 1 from imsld_status_user where imsld_id = :imsld_id and user_id = :user_id and completed_id = :imsld_id) + ) + + + + + + + + select ip.play_id + from imsld_plays ip, imsld_methodsi im, imsld_imsldsi ii + where ip.method_id = im.item_id + and im.imsld_id = ii.item_id + and ii.imsld_id = :imsld_id + + + + + + + + insert into imsld_status_user (imsld_id, + completed_id, + user_id, + type, + finished_date) + ( + select :imsld_id, + :method_id, + :user_id, + 'method', + now() + where not exists (select 1 from imsld_status_user where imsld_id = :imsld_id and user_id = :user_id and completed_id = :method_id) + ) + + + + + + + + select ip.play_id + from imsld_plays ip, imsld_methodsi im + where ip.method_id = im.item_id + and im.method_id = :method_id + + + + + + + + select t.table_name + from acs_object_types t + where t.object_type = :rel_type + + + + + + + + select r.rel_id + from acs_rels r + where r.rel_type = :rel_type + + + + + + + + BEGIN + acs_rel_type.drop_type( rel_type => :rel_type, + cascade_p => 't' ); + END; + + + + + + + insert into imsld_status_user ( + select :imsld_id, + :play_id, + :act_id, + :role_part_id, + :element_id, + :user_id, + :type, + now() + where not exists (select 1 from imsld_status_user where imsld_id = :imsld_id and user_id = :user_id and completed_id = :element_id) + ) + + + + + + + + select ias.structure_id, + ias.item_id as structure_item_id + from acs_rels ar, imsld_activity_structuresi ias, cr_items cri + where ar.object_id_one = ias.item_id + and ar.object_id_two = cri.item_id + and cri.live_revision = :element_id + + + + + + + + select content_item__get_live_revision(ar.object_id_two) as activity_id + from acs_rels ar + where ar.object_id_one = :structure_item_id + and ar.rel_type in ('imsld_as_la_rel','imsld_as_sa_rel','imsld_as_as_rel') + + + + + + + + select count(*) from imsld_status_user + where completed_id = :activity_id + and user_id = :user_id + + + + + + + +select 1 from imsld_status_user where completed_id = :role_part_id and user_id = :user_id + + + + + + + select ii.imsld_id, + ip.play_id, + ip.item_id as play_item_id, + ia.act_id, + ia.item_id as act_item_id, + ip.when_last_act_completed_p, + im.method_id, + im.item_id as method_item_id + from imsld_imsldsi ii, imsld_methodsi im, imsld_playsi ip, imsld_actsi ia, imsld_role_parts irp + where irp.role_part_id = :role_part_id + and irp.act_id = ia.item_id + and ia.play_id = ip.item_id + and ip.method_id = im.item_id + and im.imsld_id = ii.item_id + and content_revision__is_live(ii.imsld_id) = 't' + + + + + + + + select ar.object_id_two as role_part_item_id, + rp.role_part_id + from acs_rels ar, imsld_role_partsi rp + where ar.object_id_one = :act_item_id + and rp.item_id = ar.object_id_two + and ar.rel_type = 'imsld_act_rp_completed_rel' + and content_revision__is_live(rp.role_part_id) = 't' + + + + + + + + select irp.role_part_id + from imsld_role_parts irp + where irp.act_id = :act_item_id + and content_revision__is_live(irp.role_part_id) = 't' + + + + + + + + select ia.act_id + from imsld_acts ia, imsld_playsi ip + where ia.play_id = :play_item_id + and ip.item_id = ia.play_id + and content_revision__is_live(ia.act_id) = 't' + + + + + + + + select ip.play_id + from acs_rels ar, imsld_playsi ip + where ar.object_id_one = :method_item_id + and ip.item_id = ar.object_id_two + and ar.rel_type = 'imsld_mp_completed_rel' + and content_revision__is_live(ip.play_id) = 't' + + + + + + + + select ip.play_id + from imsld_plays ip + where ip.method_id = :method_item_id + and content_revision__is_live(ip.play_id) = 't' + + + + + + + + select ar.object_id_two, + ar.rel_type + from acs_rels ar, imsld_activity_structuresi ias + where ar.object_id_one = ias.item_id + and ias.structure_id = :activity_structure_id + order by ar.object_id_two + + + + + + + + select sort_order, + activity_id as learning_activity_id + from imsld_learning_activitiesi + where item_id = :object_id_two + and content_revision__is_live(activity_id) = 't' + + + + + + + + select count(*) + from imsld_status_user + where completed_id = :learning_activity_id + and user_id = :user_id + + + + + + + + select sort_order, + activity_id as support_activity_id + from imsld_support_activitiesi + where item_id = :object_id_two + and content_revision__is_live(activity_id) = 't' + + + + + + + + select count(*) + from imsld_status_user + where completed_id = :support_activity_id + and user_id = :user_id + + + + + + + + select sort_order, structure_id, item_id + from imsld_activity_structuresi + where item_id = :object_id_two + and content_revision__is_live(structure_id) = 't' + + + + + + + + + select count(*) + from imsld_status_user + where completed_id = :structure_id + and user_id = :user_id + + + + + + + + select 1 + from imsld_status_user + where completed_id = :role_part_id + and user_id = :user_id + + + + + + + + select case + when learning_activity_id is not null + then 'learning' + when support_activity_id is not null + then 'support' + when activity_structure_id is not null + then 'structure' + else 'none' + end as type, + learning_activity_id, + support_activity_id, + activity_structure_id + from imsld_role_parts + where role_part_id = :role_part_id + + + + + + + + select count(*) from imsld_status_user + where completed_id = content_item__get_live_revision(:learning_activity_id) + and user_id = :user_id + + + + + + + + select count(*) from imsld_status_user + where completed_id = content_item__get_live_revision(:support_activity_id) + and user_id = :user_id + + + + + + + + select count(*) from imsld_status_user + where completed_id = content_item__get_live_revision(:activity_structure_id) + and user_id = :user_id + + + + + + + + select 1 + from imsld_status_user + where completed_id = :act_id + and user_id = :user_id + + + + + + + + select 1 + from imsld_status_user + where completed_id = :play_id + and user_id = :user_id + + + + + + + + select 1 + from imsld_status_user + where completed_id = :method_id + and user_id = :user_id + + + + + + + + select 1 + from imsld_status_user + where completed_id = :imsld_id + and user_id = :user_id + + + + + + + + select serv.service_id, + serv.identifier, + serv.class, + serv.is_visible_p, + serv.service_type + from imsld_servicesi serv + where serv.item_id = :service_item_id + and content_revision__is_live(serv.service_id) = 't' + + + + + + + + select conf.conference_id, + conf.conference_type, + conf.imsld_item_id as imsld_item_item_id, + cr.live_revision as imsld_item_id + from imsld_conference_services conf, cr_items cr + where conf.service_id = :service_item_id + and cr.item_id = conf.imsld_item_id + and content_revision__is_live(cr.live_revision) = 't' + + + + + + + + select cpr.resource_id, + cpr.item_id as resource_item_id, + cpr.type as resource_type + from imsld_cp_resourcesi cpr, imsld_itemsi ii, + acs_rels ar + where ar.object_id_one = ii.item_id + and ar.object_id_two = cpr.item_id + and content_revision__is_live(cpr.resource_id) = 't' + and (imsld_tree_sortkey between tree_left((select imsld_tree_sortkey from imsld_items where imsld_item_id = :imsld_item_id)) + and tree_right((select imsld_tree_sortkey from imsld_items where imsld_item_id = :imsld_item_id)) + or ii.imsld_item_id = :imsld_item_id) + + + + + + + + select env.title as environment_title, + env.environment_id + from imsld_environmentsi env + where env.item_id = :environment_item_id + and content_revision__is_live(env.environment_id) = 't' + + + + + + + + select item_id as learning_object_item_id, + learning_object_id, + identifier + from imsld_learning_objectsi + where environment_id = :environment_item_id + and content_revision__is_live(learning_object_id) = 't' + + + + + + + + select ii.imsld_item_id + from imsld_items ii, + cr_items cr, + acs_rels ar + where ar.object_id_one = :learning_object_item_id + and ar.object_id_two = cr.item_id + and cr.live_revision = ii.imsld_item_id + + + + + + + + select cpr.resource_id, + cpr.item_id as resource_item_id, + cpr.type as resource_type + from imsld_cp_resourcesi cpr, imsld_itemsi ii, + acs_rels ar + where ar.object_id_one = ii.item_id + and ar.object_id_two = cpr.item_id + and content_revision__is_live(cpr.resource_id) = 't' + and (imsld_tree_sortkey between tree_left((select imsld_tree_sortkey from imsld_items where imsld_item_id = :imsld_item_id)) + and tree_right((select imsld_tree_sortkey from imsld_items where imsld_item_id = :imsld_item_id)) + or ii.imsld_item_id = :imsld_item_id) + + + + + + + + select service_id, + item_id as service_item_id, + identifier, + service_type + from imsld_servicesi + where environment_id = :environment_item_id + and content_revision__is_live(service_id) = 't' + + + + + + + + select ar.object_id_two as nested_environment_item_id + from acs_rels ar + where ar.object_id_one = :environment_item_id + and ar.rel_type = 'imsld_env_env_rel' + + + + + + + + + select learning_objective_id as learning_objective_item_id + from imsld_imsldsi + where item_id = :imsld_item_id + and content_revision__is_live(imsld_id) = 't' + + + + + + + + + select learning_objective_id as learning_objective_item_id + from imsld_learning_activitiesi + where item_id = :activity_item_id + and content_revision__is_live(activity_id) = 't' + + + + + + + + select coalesce(lo.pretty_title, '') as objective_title, + lo.learning_objective_id + from imsld_learning_objectivesi lo + where lo.item_id = :learning_objective_item_id + and content_revision__is_live(lo.learning_objective_id) = 't' + + + + + + + + select ii.imsld_item_id + from imsld_items ii, + cr_items cr, acs_rels ar + where ar.object_id_one = :learning_objective_item_id + and ar.object_id_two = cr.item_id + and cr.live_revision = ii.imsld_item_id + + + + + + + + select cpr.resource_id, + cpr.item_id as resource_item_id, + cpr.type as resource_type + from imsld_cp_resourcesi cpr, imsld_itemsi ii, + acs_rels ar + where ar.object_id_one = ii.item_id + and ar.object_id_two = cpr.item_id + and content_revision__is_live(cpr.resource_id) = 't' + and (imsld_tree_sortkey between tree_left((select imsld_tree_sortkey from imsld_items where imsld_item_id = :imsld_item_id)) + and tree_right((select imsld_tree_sortkey from imsld_items where imsld_item_id = :imsld_item_id)) + or ii.imsld_item_id = :imsld_item_id) + + + + + + + + + select prerequisite_id as prerequisite_item_id + from imsld_imsldsi + where item_id = :imsld_item_id + and content_revision__is_live(imsld_id) = 't' + + + + + + + + + select prerequisite_id as prerequisite_item_id + from imsld_learning_activitiesi + where item_id = :activity_item_id + and content_revision__is_live(activity_id) = 't' + + + + + + + + select coalesce(pre.pretty_title, '') as prerequisite_title, + pre.prerequisite_id + from imsld_prerequisitesi pre + where pre.item_id = :prerequisite_item_id + and content_revision__is_live(pre.prerequisite_id) = 't' + + + + + + + + select ii.imsld_item_id + from imsld_items ii, + cr_items cr, acs_rels ar + where ar.object_id_one = :prerequisite_item_id + and ar.object_id_two = cr.item_id + and cr.live_revision = ii.imsld_item_id + + + + + + + + select cpr.resource_id, + cpr.item_id as resource_item_id, + cpr.type as resource_type + from imsld_cp_resourcesi cpr, imsld_itemsi ii, + acs_rels ar + where ar.object_id_one = ii.item_id + and ar.object_id_two = cpr.item_id + and content_revision__is_live(cpr.resource_id) = 't' + and (imsld_tree_sortkey between tree_left((select imsld_tree_sortkey from imsld_items where imsld_item_id = :imsld_item_id)) + and tree_right((select imsld_tree_sortkey from imsld_items where imsld_item_id = :imsld_item_id)) + or ii.imsld_item_id = :imsld_item_id) + + + + + + + + select coalesce(oc.feedback_title, oc.title) as feedback_title + from imsld_on_completioni oc + where oc.item_id = :on_completion_item_id + and content_revision__is_live(oc.on_completion_id) = 't' + + + + + + + + select ii.imsld_item_id + from imsld_items ii, + cr_items cr, acs_rels ar + where ar.object_id_one = :on_completion_item_id + and ar.object_id_two = cr.item_id + and cr.live_revision = ii.imsld_item_id + + + + + + + + select cpr.resource_id, + cpr.item_id as resource_item_id, + cpr.type as resource_type + from imsld_cp_resourcesi cpr, imsld_itemsi ii, + acs_rels ar + where ar.object_id_one = ii.item_id + and ar.object_id_two = cpr.item_id + and content_revision__is_live(cpr.resource_id) = 't' + and (imsld_tree_sortkey between tree_left((select imsld_tree_sortkey from imsld_items where imsld_item_id = :imsld_item_id)) + and tree_right((select imsld_tree_sortkey from imsld_items where imsld_item_id = :imsld_item_id)) + or ii.imsld_item_id = :imsld_item_id) + + + + + + + + select identifier, + type as resource_type, + title as resource_title, + acs_object_id + from imsld_cp_resourcesi + where item_id = :resource_item_id + and content_revision__is_live(resource_id) = 't' + + + + + + + + select live_revision from cr_items where item_id = :acs_object_id + + + + + + + + + select acs_object__name(object_id) as object_title, object_type + from acs_objects where object_id = :live_revision + + + + + + + + + select acs_object__name(object_id) as object_title, object_type + from acs_objects where object_id = :acs_object_id + + + + + + + + select cpf.imsld_file_id, + cpf.file_name, + cpf.item_id, cpf.parent_id + from imsld_cp_filesx cpf, + acs_rels ar + where ar.object_id_one = :resource_item_id + and ar.object_id_two = cpf.item_id + and content_revision__is_live(cpf.imsld_file_id) = 't' + + + + + + + + select content_item__get_path(:parent_id,:root_folder_id); + + + + + + + select + case + when :folder_path is null + then fs.file_upload_name + else :folder_path || '/' || fs.file_upload_name + end as file_url + from fs_objects fs + where fs.live_revision = :imsld_file_id + + + + + + + + select url + from acs_rels ar, + cr_extlinks links + where ar.object_id_one = :resource_item_id + and ar.object_id_two = links.extlink_id + + + + + + + + select on_completion_id as on_completion_item_id, + prerequisite_id as prerequisite_item_id, + learning_objective_id as learning_objective_item_id, + activity_id + from imsld_learning_activitiesi + where item_id = :activity_item_id + and content_revision__is_live(activity_id) = 't' + + + + + + + + select ar.object_id_two as environment_item_id + from acs_rels ar + where ar.object_id_one = :activity_item_id + and ar.rel_type = 'imsld_la_env_rel' + order by ar.object_id_two + + + + + + + + select ii.imsld_item_id + from imsld_items ii, imsld_activity_descs lad, imsld_learning_activitiesi la, + cr_items cr1, cr_items cr2, + acs_rels ar + where la.item_id = :activity_item_id + and la.activity_description_id = cr1.item_id + and cr1.live_revision = lad.description_id + and ar.object_id_one = la.activity_description_id + and ar.object_id_two = cr2.item_id + and cr2.live_revision = ii.imsld_item_id + + + + + + + + select cpr.resource_id, + cpr.item_id as resource_item_id, + cpr.type as resource_type + from imsld_cp_resourcesi cpr, imsld_itemsi ii, + acs_rels ar + where ar.object_id_one = ii.item_id + and ar.object_id_two = cpr.item_id + and content_revision__is_live(cpr.resource_id) = 't' + and (imsld_tree_sortkey between tree_left((select imsld_tree_sortkey from imsld_items where imsld_item_id = :imsld_item_id)) + and tree_right((select imsld_tree_sortkey from imsld_items where imsld_item_id = :imsld_item_id)) + or ii.imsld_item_id = :imsld_item_id) + + + + + + + + select on_completion_id as on_completion_item_id, + activity_id + from imsld_support_activitiesi + where item_id = :activity_item_id + and content_revision__is_live(activity_id) = 't' + + + + + + + + select ar.object_id_two as environment_item_id + from acs_rels ar + where ar.object_id_one = :activity_item_id + and ar.rel_type = 'imsld_sa_env_rel' + order by ar.object_id_two + + + + + + + + select ii.imsld_item_id + from imsld_items ii, imsld_activity_descs sad, imsld_support_activitiesi sa, + cr_items cr1, cr_items cr2, + acs_rels ar + where sa.item_id = :activity_item_id + and sa.activity_description_id = cr1.item_id + and cr1.live_revision = sad.description_id + and ar.object_id_one = sa.activity_description_id + and ar.object_id_two = cr2.item_id + and cr2.live_revision = ii.imsld_item_id + + + + + + + + select cpr.resource_id, + cpr.item_id as resource_item_id, + cpr.type as resource_type + from imsld_cp_resourcesi cpr, imsld_itemsi ii, + acs_rels ar + where ar.object_id_one = ii.item_id + and ar.object_id_two = cpr.item_id + and content_revision__is_live(cpr.resource_id) = 't' + and (imsld_tree_sortkey between tree_left((select imsld_tree_sortkey from imsld_items where imsld_item_id = :imsld_item_id)) + and tree_right((select imsld_tree_sortkey from imsld_items where imsld_item_id = :imsld_item_id)) + or ii.imsld_item_id = :imsld_item_id) + + + + + + + + select ar.object_id_two as environment_item_id + from acs_rels ar + where ar.object_id_one = :structure_item_id + and ar.rel_type = 'imsld_as_env_rel' + order by ar.object_id_two + + + + + + + + select imsld_id + from imsld_imsldsi + where item_id = :imsld_item_id + and content_revision__is_live(imsld_id) = 't' + + + + + + + + select count(*) + from imsld_status_user + where user_id = :user_id + and imsld_id = :imsld_id + + + + + + + + select irp.role_part_id + from cr_items cr0, cr_items cr1, cr_items cr2, imsld_methods im, imsld_plays ip, imsld_acts ia, imsld_role_parts irp + where im.imsld_id = :imsld_item_id + and ip.method_id = cr0.item_id + and cr0.live_revision = im.method_id + and ia.play_id = cr1.item_id + and cr1.live_revision = ip.play_id + and irp.act_id = cr2.item_id + and cr2.live_revision = ia.act_id + and content_revision__is_live(irp.role_part_id) = 't' + and ip.sort_order = (select min(ip2.sort_order) from imsld_plays ip2 where ip2.method_id = cr0.item_id) + and ia.sort_order = (select min(ia2.sort_order) from imsld_acts ia2 where ia2.play_id = cr1.item_id) + and irp.sort_order = (select min(irp2.sort_order) from imsld_role_parts irp2 where irp2.act_id = cr2.item_id) + + + + + + + + + select stat.completed_id, + stat.role_part_id, + stat.type, + rp.sort_order, + rp.act_id + from imsld_status_user stat, imsld_role_parts rp + where stat.imsld_id = :imsld_id + and stat.user_id = :user_id + and stat.role_part_id = rp.role_part_id + order by stat.finished_date + + + + + + + + select coalesce(title,identifier) as activity_title, + item_id as activity_item_id + from imsld_learning_activitiesi + where activity_id = :completed_id + + + + + + + + select coalesce(title,identifier) as activity_title, + item_id as activity_item_id + from imsld_support_activitiesi + where activity_id = :completed_id + + + + + + + + select coalesce(title,identifier) as activity_title, + item_id as structure_item_id + from imsld_activity_structuresi + where structure_id = :completed_id + + + + + + + + select role_part_id + from imsld_role_parts + where sort_order = :sort_order + 1 + and act_id = :act_id + + + + + + + + select ip.item_id as play_item_id, + ip.play_id, + ia.sort_order as act_sort_order + from imsld_playsi ip, imsld_acts ia, cr_items cr + where ip.item_id = ia.play_id + and ia.act_id = cr.live_revision + and cr.item_id = :act_id + + + + + + + + select rp.role_part_id + from imsld_role_parts rp, imsld_actsi ia + where ia.play_id = :play_item_id + and ia.sort_order = :act_sort_order + 1 + and rp.act_id = ia.item_id + and content_revision__is_live(rp.role_part_id) = 't' + and content_revision__is_live(ia.act_id) = 't' + and rp.sort_order = (select min(irp2.sort_order) from imsld_role_parts irp2 where irp2.act_id = rp.act_id) + + + + + + + + select im.item_id as method_item_id, + ip.sort_order as play_sort_order + from imsld_methodsi im, imsld_plays ip + where im.item_id = ip.method_id + and ip.play_id = :play_id + + + + + + + + select rp.role_part_id + from imsld_role_parts rp, imsld_actsi ia, imsld_playsi ip + where ip.method_id = :method_item_id + and ia.play_id = ip.item_id + and rp.act_id = ia.item_id + and ip.sort_order = :play_sort_order + 1 + and content_revision__is_live(rp.role_part_id) = 't' + and content_revision__is_live(ia.act_id) = 't' + and content_revision__is_live(ip.play_id) = 't' + and ia.sort_order = (select min(ia2.sort_order) from imsld_acts ia2 where ia2.play_id = ip.item_id) + and rp.sort_order = (select min(irp2.sort_order) from imsld_role_parts irp2 where irp2.act_id = ia.item_id) + + + + + + + + select case + when learning_activity_id is not null + then 'learning' + when support_activity_id is not null + then 'support' + when activity_structure_id is not null + then 'structure' + else 'none' + end as activity_type, + case + when learning_activity_id is not null + then content_item__get_live_revision(learning_activity_id) + when support_activity_id is not null + then content_item__get_live_revision(support_activity_id) + when activity_structure_id is not null + then content_item__get_live_revision(activity_structure_id) + else content_item__get_live_revision(environment_id) + end as activity_id, + environment_id as rp_environment_item_id + from imsld_role_parts + where role_part_id = :role_part_id + + + + + + + + select la.activity_id, + la.item_id as activity_item_id, + la.title as activity_title, + la.identifier, + la.user_choice_p + from imsld_learning_activitiesi la + where la.activity_id = :activity_id + + + + + + + + select sa.activity_id, + sa.item_id as activity_item_id, + sa.title as activity_title, + sa.identifier, + sa.user_choice_p + from imsld_support_activitiesi sa + where sa.activity_id = :activity_id + + + + + + + + select count(*) from imsld_status_user + where completed_id = :activity_id + and user_id = :user_id + + + + + + + + select ila.activity_id as activity_item_id + from imsld_cp_resourcesi icri, + acs_rels ar1, + acs_rels ar2, + imsld_learning_activities ila + where ar2.object_id_two=icri.item_id + and ar1.object_id_two=ar2.object_id_one + and ila.activity_description_id=ar1.object_id_one + and icri.resource_id= :resource_id + + + + + + + + + select ar1.object_id_one as imsld_item_item_id + from imsld_cp_resourcesi icri, + acs_rels ar1 + where icri.item_id=ar1.object_id_two + and icri.resource_id= :resource_id + + + + + + + +select 1 from imsld_conference_services where imsld_item_id=:imsld_item_item_id + + + + + + + select isi.environment_id as environment_item_id + from imsld_conference_services ics, + imsld_servicesi isi + where isi.item_id=ics.service_id + and ics.imsld_item_id=:imsld_item_item_id + + + + + + + + select ila.activity_id as activity_item_id + from acs_rels ar, + imsld_learning_activitiesi ila + where ila.item_id=ar.object_id_one + and ar.object_id_two=:environment_item_id; + + + + + + + +select 1 from acs_rels where rel_type='imsld_l_object_item_rel' and object_id_two=:imsld_item_item_id + + + + + + + select iloi.environment_id as environment_item_id + from imsld_learning_objectsi iloi, + acs_rels ar + where iloi.item_id=ar.object_id_one + and ar.object_id_two=:imsld_item_item_id + + + + + + + + + + select ar1.object_id_one as resource_element_id + from acs_rels ar1, + acs_rels ar2, + imsld_cp_resourcesi icr + where ar1.object_id_two=ar2.object_id_one + and ar2.object_id_two=icr.item_id + and icr.resource_id = :resource_id; + + + + + + + + select 1 from imsld_prerequisitesi where item_id=:resource_element_id + + + + + + + select activity_id as activity_item_id + from imsld_learning_activitiesi + where prerequisite_id=:resource_element_id + + + + + + + + select 1 from imsld_learning_objectivesi where item_id=:resource_element_id + + + + + + + select activity_id as activity_item_id + from imsld_learning_activitiesi + where learning_objective_id=:resource_element_id + + + + + + + + select irp.role_part_id as role_part_id + from imsld_role_parts irp, + imsld_learning_activitiesi ilai, + acs_rels ar + where ilai.activity_id=:activity_id + and ar.object_id_two=ilai.item_id + and irp.activity_structure_id=ar.object_id_one + + + + + + + + select iii.imsld_id as imsld_id + from imsld_imsldsi iii, + cr_items cr, + cr_items cr2, + imsld_learning_activitiesi ilai + where ilai.activity_id=:activity_id + and ilai.item_id=cr.item_id + and cr2.parent_id=cr.parent_id + and cr2.content_type='imsld_imsld' + and iii.item_id=cr2.item_id + + + + + + + + select resource_id + from imsld_cp_resources + where acs_object_id = :object_id + + + + + + + + + select item_id as activity_item_id + from imsld_learning_activitiesi + where activity_id=:activity_id + + + + + + + + insert into imsld_status_user ( + imsld_id, + role_part_id, + completed_id, + user_id, + type, + finished_date + ) + ( + select :imsld_id, + :role_part_id, + :resource_id, + :user_id, + 'resource', + now() + ) + + + + + + + + select resource_id as rid + from imsld_cp_resourcesi + where item_id =:res_id + + + + + + + + select count(*) + from imsld_status_user + where completed_id = :resource_id + + + + + + + + select case + when (select 1 from imsld_learning_activities where activity_id=:activity_id)=1 + then 'learning' + when (select 1 from imsld_support_activities where activity_id=:activity_id)=1 + then 'support' + else 'none' + end as type + + + + +