select dc.community_id
from imsld_cp_manifestsi im, dotlrn_communities dc, acs_objects ao, acs_objects ao2
where im.item_id = ao.object_id
and ao.context_id = ao2.object_id
and ao2.context_id = dc.package_id
and im.manifest_id = :manifest_id
select icm.manifest_id,
ii.imsld_id,
im.method_id,
ir.run_id,
ca.time_in_seconds,
ao.creation_date
from imsld_cp_manifestsi icm, imsld_cp_organizationsi ico,
imsld_imsldsi ii, imsld_methodsi im, imsld_complete_actsi ca, imsld_runs ir, acs_objects ao
where im.imsld_id = ii.item_id
and ii.imsld_id = ir.imsld_id
and ii.organization_id = ico.item_id
and ico.manifest_id = icm.item_id
and im.complete_act_id = ca.item_id
and ca.time_in_seconds is not null
and ao.object_id = ir.run_id
and content_revision__is_live(ii.imsld_id) = 't'
select 1
where (extract(epoch from now()) - extract(epoch from timestamp :creation_date) - :time_in_seconds > 0)
select u.user_id
from users u,
acs_rels ar,
imsld_run_users_group_ext r_map
where u.user_id > 0
and u.user_id=ar.object_id_two
and ar.object_id_one = r_map.group_id
and r_map.run_id = :run_id
select icm.manifest_id,
ii.imsld_id,
ip.play_id,
ca.time_in_seconds,
ao.creation_date,
ir.run_id
from imsld_cp_manifestsi icm, imsld_cp_organizationsi ico,
imsld_imsldsi ii, imsld_methodsi im, imsld_plays ip,
imsld_complete_actsi ca, imsld_runs ir, acs_objects ao
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.complete_act_id = ca.item_id
and ca.time_in_seconds is not null
and ao.object_id = ir.run_id
and content_revision__is_live(ii.imsld_id) = 't'
and ii.imsld_id = ir.imsld_id
select icm.manifest_id,
ii.imsld_id,
ip.play_id,
ia.act_id,
ca.time_in_seconds,
icm.creation_date,
ir.run_id
from imsld_cp_manifestsi icm, imsld_cp_organizationsi ico,
imsld_imsldsi ii, imsld_methodsi im, imsld_playsi ip, imsld_acts ia,
imsld_complete_actsi ca, imsld_runs ir, acs_objects ao
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.complete_act_id = ca.item_id
and ca.time_in_seconds is not null
and ao.object_id = ir.run_id
and content_revision__is_live(ii.imsld_id) = 't'
and ii.imsld_id = ir.imsld_id
select sa.item_id as sa_item_id,
sa.activity_id,
ca.time_in_seconds
from imsld_support_activitiesi sa,
imsld_complete_actsi ca
where sa.complete_act_id = ca.item_id
and content_revision__is_live(ca.complete_act_id) = 't'
and ca.time_in_seconds is not null
select icm.manifest_id,
irp.role_part_id,
ii.imsld_id,
ip.play_id,
ia.act_id,
ao.creation_date,
ir.run_id
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_runs ir, acs_objects ao
where irp.support_activity_id = :sa_item_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 ii.imsld_id = ir.imsld_id
and ao.object_id = ir.run_id
and ico.manifest_id = icm.item_id
and content_revision__is_live(ii.imsld_id) = 't'
select la.item_id as la_item_id,
la.activity_id,
ca.time_in_seconds
from imsld_learning_activitiesi la,
imsld_complete_actsi ca
where la.complete_act_id = ca.item_id
and content_revision__is_live(ca.complete_act_id) = 't'
and ca.time_in_seconds is not null
select icm.manifest_id,
irp.role_part_id,
ii.imsld_id,
ip.play_id,
ia.act_id,
ao.creation_date,
ir.run_id
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_runs ir, acs_objects ao
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 ii.organization_id = ico.item_id
and ii.imsld_id = ir.imsld_id
and ao.object_id = ir.run_id
and ico.manifest_id = icm.item_id
and content_revision__is_live(ii.imsld_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,
run_id,
play_id,
act_id,
related_id,
user_id,
type,
status_date,
status)
(
select :imsld_id,
:run_id,
:play_id,
:act_id,
:role_part_id,
:user_id,
'act',
now(),
'finished'
where not exists (select 1 from imsld_status_user where run_id = :run_id and user_id = :user_id and related_id = :role_part_id and status = 'finished')
)
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,
coalesce(learning_activity_id, support_activity_id, activity_structure_id) as activity_item_id
from imsld_role_parts
where role_part_id = :role_part_id
select 1
from forums_forums ff
where ff.forum_id=:the_object_id
select acs_object_id as the_object_id
from imsld_cp_resourcesi
where item_id = :the_resource_id and
acs_object_id is not null
select ar.object_id_two as related_cr_items
from acs_rels ar
where ar.object_id_one=:the_resource_id and
ar.rel_type='imsld_res_files_rel'
select item_id as act_item_id
from imsld_actsi
where act_id = :act_id
insert into imsld_status_user (imsld_id,
run_id,
play_id,
related_id,
user_id,
type,
status_date,
status)
(
select :imsld_id,
:run_id,
:play_id,
:act_id,
:user_id,
'act',
now(),
'finished'
where not exists (select 1 from imsld_status_user where run_id = :run_id and user_id = :user_id and related_id = :act_id and status = 'finished')
)
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,
run_id,
related_id,
user_id,
type,
status_date,
status)
(
select :imsld_id,
:run_id,
:play_id,
:user_id,
'play',
now(),
'finished'
where not exists (select 1 from imsld_status_user where run_id = :run_id and user_id = :user_id and related_id = :play_id and status = 'finished')
)
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,
run_id,
related_id,
user_id,
type,
status_date,
status)
(
select :imsld_id,
:run_id,
:imsld_id,
:user_id,
'play',
now(),
'finished'
where not exists (select 1 from imsld_status_user where run_id = :run_id and user_id = :user_id and related_id = :imsld_id and status = 'finished')
)
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,
run_id,
related_id,
user_id,
type,
status_date,
status)
(
select :imsld_id,
:run_id,
:method_id,
:user_id,
'method',
now(),
'finished'
where not exists (select 1 from imsld_status_user where run_id = :run_id and user_id = :user_id and related_id = :method_id and status = 'finished')
)
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;
select on_completion_id as related_on_completion
from $table_name
where $element_name=:element_id and
on_completion_id is not null
select ar2.object_id_two as related_resource
from acs_rels ar1,
acs_rels ar2
where ar2.object_id_one=ar1.object_id_two and
ar2.rel_type='imsld_item_res_rel' and
ar1.rel_type='imsld_feedback_rel' and
ar1.object_id_one=:related_on_completion
insert into imsld_status_user (
imsld_id,
run_id,
play_id,
act_id,
role_part_id,
related_id,
user_id,
type,
status_date,
status
)
(
select :imsld_id,
:run_id,
:play_id,
:act_id,
:role_part_id,
:element_id,
:user_id,
:type,
now(),
'finished'
where not exists (select 1 from imsld_status_user where run_id = :run_id and user_id = :user_id and related_id = :element_id and status = 'finished')
)
select ias.structure_id,
ias.item_id as structure_item_id,
ias.number_to_select
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 related_id = :activity_id
and user_id = :user_id
and status = 'finished'
and run_id = :run_id
select 1 from imsld_status_user
where related_id = :role_part_id
and user_id = :user_id
and status = 'finished'
and run_id = :run_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,
ica.when_last_act_completed_p,
im.method_id,
im.item_id as method_item_id
from imsld_imsldsi ii, imsld_actsi ia, imsld_role_parts irp,
imsld_methodsi im, imsld_playsi ip left outer join imsld_complete_actsi ica on (ip.complete_act_id = ica.item_id)
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,
imsld_rolesi iri
where content_revision__is_live(irp.role_part_id)='t'
and irp.act_id=:act_item_id
and irp.role_id=iri.item_id
and iri.role_id in ([join $user_roles_list ","])
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,
ar.rel_id
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 la.activity_id as learning_activity_id
from imsld_learning_activitiesi la
where la.item_id = :object_id_two
and content_revision__is_live(la.activity_id) = 't'
select count(*)
from imsld_status_user
where related_id = :learning_activity_id
and user_id = :user_id
and status = 'finished'
and run_id = :run_id
select 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 related_id = :support_activity_id
and user_id = :user_id
and status = 'finished'
and run_id = :run_id
select structure_id, title,
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 related_id = :structure_id
and user_id = :user_id
and status = 'finished'
and run_id = :run_id
select 1
from imsld_status_user
where related_id = :role_part_id
and user_id = :user_id
and status = 'finished'
and run_id = :run_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 related_id = content_item__get_live_revision(:learning_activity_id)
and user_id = :user_id
and status = 'finished'
and run_id = :run_id
select count(*) from imsld_status_user
where related_id = content_item__get_live_revision(:support_activity_id)
and user_id = :user_id
and status = 'finished'
and run_id = :run_id
select count(*) from imsld_status_user
where related_id = content_item__get_live_revision(:activity_structure_id)
and user_id = :user_id
and status = 'finished'
and run_id = :run_id
select 1
from imsld_status_user
where related_id = :act_id
and user_id = :user_id
and status = 'finished'
and run_id = :run_id
select 1
from imsld_status_user
where related_id = :play_id
and user_id = :user_id
and status = 'finished'
and run_id = :run_id
select 1
from imsld_status_user
where related_id = :method_id
and user_id = :user_id
and status = 'finished'
and run_id = :run_id
select 1
from imsld_status_user
where related_id = :imsld_id
and user_id = :user_id
and status = 'finished'
and run_id = :run_id
select serv.service_id,
serv.identifier,
serv.service_type,
serv.title as service_title
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,
conf.title as conf_title
from imsld_conference_servicesi 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 sm.title as send_mail_title, sm.mail_id as sendmail_id
from imsld_send_mail_servicesi sm
where sm.service_id = :service_item_id
and content_revision__is_live(sm.mail_id) = 't'
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,
coalesce(title,identifier) as lo_title,
class
from imsld_learning_objectsi, imsld_attribute_instances attr
where environment_id = :environment_item_id
and content_revision__is_live(learning_object_id) = 't'
and attr.owner_id = learning_object_id
and attr.run_id = :run_id
and attr.type = 'isvisible'
and attr.is_visible_p = 't'
order by creation_date
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,
cr2.item_id as resource_item_id,
cpr.type as resource_type
from imsld_cp_resources cpr, imsld_items ii, imsld_attribute_instances attr,
acs_rels ar, cr_items cr1, cr_items cr2
where ar.object_id_one = cr1.item_id
and ar.object_id_two = cr2.item_id
and cr1.live_revision = ii.imsld_item_id
and cr2.live_revision = cpr.resource_id
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)
and attr.owner_id = ii.imsld_item_id
and attr.run_id = :run_id
and attr.type = 'isvisible'
and attr.is_visible_p = 't'
select service_id,
item_id as service_item_id,
identifier,
service_type,
title as service_title,
class
from imsld_servicesi, imsld_attribute_instances attr
where environment_id = :environment_item_id
and content_revision__is_live(service_id) = 't'
and attr.owner_id = service_id
and attr.run_id = :run_id
and attr.type = 'isvisible'
and attr.is_visible_p = '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 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, imsld_attribute_instances attr,
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)
and attr.owner_id = ii.imsld_item_id
and attr.run_id = :run_id
and attr.type = 'isvisible'
and attr.is_visible_p = 't'
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, imsld_attribute_instances attr,
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)
and attr.owner_id = ii.imsld_item_id
and attr.run_id = :run_id
and attr.type = 'isvisible'
and attr.is_visible_p = 't'
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, imsld_attribute_instances attr,
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)
and attr.owner_id = ii.imsld_item_id
and attr.run_id = :run_id
and attr.type = 'isvisible'
and attr.is_visible_p = 't'
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, imsld_res_files_rels map
where ar.object_id_one = :resource_item_id
and ar.object_id_two = cpf.item_id
and ar.rel_id = map.rel_id
and content_revision__is_live(cpf.imsld_file_id) = 't'
and map.displayable_p = '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,
imsld_res_files_rels map
where ar.object_id_one = :resource_item_id
and ar.object_id_two = links.extlink_id
and ar.rel_id = map.rel_id
and map.displayable_p = 't'
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,
title as activity_title
from imsld_learning_activitiesi, imsld_attribute_instances attr
where item_id = :activity_item_id
and content_revision__is_live(activity_id) = 't'
and attr.owner_id = activity_id
and attr.run_id = :run_id
and attr.type = 'isvisible'
and attr.is_visible_p = 't'
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, imsld_attribute_instances attr,
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)
and attr.owner_id = ii.imsld_item_id
and attr.run_id = :run_id
and attr.type = 'isvisible'
and attr.is_visible_p = 't'
select 1
from imsld_status_user
where user_id = :user_id
and related_id = :activity_id
and run_id = :run_id
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 on_completion_id as on_completion_item_id,
activity_id,
attr.is_visible_p
from imsld_support_activitiesi, imsld_attribute_instances attr
where item_id = :activity_item_id
and content_revision__is_live(activity_id) = 't'
and attr.owner_id = activity_id
and attr.run_id = :run_id
and attr.type = 'isvisible'
and attr.is_visible_p = 't'
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, imsld_attribute_instances attr,
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)
and attr.owner_id = ii.imsld_item_id
and attr.run_id = :run_id
and attr.type = 'isvisible'
and attr.is_visible_p = 't'
select 1
from imsld_status_user
where user_id = :user_id
and related_id = :activity_id
and run_id = :run_id
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_itemsi ii, acs_rels ar
where ar.object_id_one = :structure_item_id
and ar.rel_type = 'imsld_as_info_i_rel'
and ar.object_id_two = ii.item_id
and content_revision__is_live(ii.imsld_item_id) = '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, imsld_attribute_instances attr,
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)
and attr.owner_id = ii.imsld_item_id
and attr.run_id = :run_id
and attr.type = 'isvisible'
and attr.is_visible_p = 't'
select structure_id,
structure_type
from imsld_activity_structuresi
where item_id = :structure_item_id
select ar.object_id_two,
ar.rel_type,
ar.rel_id
from acs_rels ar, imsld_activity_structuresi ias
where ar.object_id_one = ias.item_id
and ias.structure_id = :structure_id
order by ar.object_id_two
select title as activity_title,
item_id as activity_item_id,
activity_id,
complete_act_id,
is_visible_p
from imsld_learning_activitiesi
where item_id = :object_id_two
and content_revision__is_live(activity_id) = 't'
select 1 from imsld_status_user
where related_id = :activity_id
and user_id = :user_id
and status = 'finished'
and run_id = :run_id
select title as activity_title,
item_id as activity_item_id,
activity_id,
complete_act_id,
is_visible_p
from imsld_support_activitiesi
where item_id = :object_id_two
and content_revision__is_live(activity_id) = 't'
select title as activity_title,
item_id as structure_item_id,
structure_id,
structure_type
from imsld_activity_structuresi
where item_id = :object_id_two
and content_revision__is_live(structure_id) = 't'
select 1 from imsld_status_user
where related_id = :structure_id
and user_id = :user_id
and status = 'started'
and run_id = :run_id
select case
when rp.learning_activity_id is not null
then 'learning'
when rp.support_activity_id is not null
then 'support'
when rp.activity_structure_id is not null
then 'structure'
else 'none'
end as type,
content_item__get_live_revision(coalesce(rp.learning_activity_id,rp.support_activity_id,rp.activity_structure_id)) as activity_id,
rp.role_part_id,
ia.act_id,
ip.play_id
from imsld_role_partsi rp, imsld_actsi ia, imsld_playsi ip, imsld_imsldsi ii, imsld_attribute_instances attr,
imsld_methodsi im,imsld_rolesi iri
where rp.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.imsld_id = :imsld_id
and rp.role_id = iri.item_id
and iri.role_id in ([join $user_roles_list ","])
and content_revision__is_live(rp.role_part_id) = 't'
and attr.owner_id = ip.play_id
and attr.run_id = :run_id
and attr.type = 'isvisible'
and attr.is_visible_p = 't'
order by ip.sort_order, ia.sort_order, rp.sort_order
select title as activity_title,
item_id as activity_item_id,
activity_id,
is_visible_p,
complete_act_id
from imsld_learning_activitiesi
where activity_id = :activity_id
select title as activity_title,
item_id as activity_item_id,
activity_id,
is_visible_p,
complete_act_id
from imsld_support_activitiesi
where activity_id = :activity_id
select title as activity_title,
item_id as structure_item_id,
structure_id,
structure_type
from imsld_activity_structuresi
where structure_id = :activity_id
select 1 from imsld_status_user
where related_id = :activity_id
and user_id = :user_id
and status = 'started'
and run_id = :run_id
select acs_object_id as assessment_id
from imsld_cp_resourcesi
where type='imsqti_xmlv1p0'
and item_id=:resource_activity
select sn.node_id as node_id
from acs_objects ao,
site_nodes sn
where ao.package_id=sn.object_id
and ao.object_id=:assessment_id;
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 run_id = :run_id
and type in ('learning','support','structure')
select irp.role_part_id, ia.act_id, ip.play_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.related_id,
stat.role_part_id,
stat.type,
rp.sort_order,
rp.act_id,
stat.status
from imsld_status_user stat, imsld_role_parts rp
where stat.run_id = :run_id
and stat.user_id = :user_id
and stat.role_part_id = rp.role_part_id
and stat.type in ('learning','support','structure')
order by stat.status_date
select title as activity_title,
item_id as activity_item_id
from imsld_learning_activitiesi
where activity_id = :related_id
select title as activity_title,
item_id as activity_item_id
from imsld_support_activitiesi
where activity_id = :related_id
select title as activity_title,
item_id as structure_item_id
from imsld_activity_structuresi
where structure_id = :related_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,
coalesce(learning_activity_id,support_activity_id,activity_structure_id) as activity_item_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.component_id
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
from imsld_support_activitiesi sa
where sa.activity_id = :activity_id
select count(*) from imsld_status_user
where related_id = :activity_id
and user_id = :user_id
and status = 'finished'
and run_id = :run_id
select ila.activity_id,
ila.item_id as activity_item_id
from imsld_cp_resourcesi icri,
acs_rels ar1,
acs_rels ar2,
imsld_learning_activitiesi 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 isa.activity_id,
isa.item_id as activity_item_id
from imsld_cp_resourcesi icri,
acs_rels ar1,
acs_rels ar2,
imsld_support_activitiesi isa
where ar2.object_id_two=icri.item_id
and ar1.object_id_two=ar2.object_id_one
and isa.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,
ila.item_id as activity_item_id,
'learning'
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 isa.activity_id,
isa.item_id as activity_item_id,
'learning'
from acs_rels ar,
imsld_support_activitiesi isa
where isa.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,
item_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,
item_id as activity_item_id
from imsld_learning_activitiesi
where learning_objective_id=:resource_element_id
select role_part_id
from imsld_role_parts
where learning_activity_id = :leaf_id
select role_part_id
from imsld_role_parts
where support_activity_id = :leaf_id
select role_part_id
from imsld_role_partsi
where activity_structure_id = :leaf_id
select ias.structure_id, ias.item_id as leaf_id
from imsld_activity_structuresi ias, acs_rels ar, imsld_learning_activitiesi la
where ar.object_id_one = ias.item_id
and ar.object_id_two = la.item_id
and content_revision__is_live(ias.structure_id) = 't'
and la.item_id = :leaf_id
select ias.structure_id, ias.item_id as leaf_id
from imsld_activity_structuresi ias, acs_rels ar, imsld_support_activitiesi sa
where ar.object_id_one = ias.item_id
and ar.object_id_two = sa.item_id
and content_revision__is_live(ias.structure_id) = 't'
and sa.item_id = :leaf_id
select ias.structure_id, ias.item_id as leaf_id
from imsld_activity_structuresi ias, acs_rels ar
where ar.object_id_one = ias.item_id
and ar.object_id_two = :leaf_id
and content_revision__is_live(ias.structure_id) = 't'
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 iii.imsld_id as imsld_id
from imsld_imsldsi iii,
cr_items cr,
cr_items cr2,
imsld_support_activitiesi isai
where isai.activity_id=:activity_id
and isai.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 iii.imsld_id as imsld_id
from imsld_imsldsi iii,
cr_items cr,
cr_items cr2,
imsld_activity_structuresi iasi
where iasi.structure_id=:activity_id
and iasi.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
insert into imsld_status_user (
imsld_id,
run_id,
related_id,
user_id,
type,
status_date,
status
)
(
select :imsld_id,
:run_id,
:resource_id,
:user_id,
'resource',
now(),
'finished'
where not exists (select 1 from imsld_status_user where run_id = :run_id and user_id = :user_id and related_id = :resource_id and status = 'finished')
)
select 1
from imsld_status_user stat, imsld_cp_resourcesi icr
where icr.item_id = :res_id
and icr.resource_id = stat.related_id
and user_id = :user_id
and status = 'finished'
and run_id = :run_id
select 1 from imsld_status_user
where related_id = :activity_id
and user_id = :user_id
and status = 'finished'
and run_id = :run_id
select count(*)
from imsld_status_user
where related_id = :resource_id
and status = 'finished'
and run_id = :run_id