postgresql7.3 select evaluation__new_item ( :item_id, --item_id :item_name, null, :creation_user, :package_id, now(), :creation_ip, :name, :description, 'text/plain', null, null, 'text', 'content_item', -- item_subtype 'evaluation_grades' -- content_type ); select evaluation__new_grade ( :item_id, :revision_id, :name, :plural_name, :weight, 'evaluation_grades', now(), --creation date :creation_user, :creation_ip, :revision_name, :description, now(), --publish date null, --nls_language 'text/plain' --mime_type ); select content_item__set_live_revision ( :revision_id ); select et.task_name, et.number_of_members, et.due_date, et.weight, et.online_p, et.late_submit_p, et.requires_grade_p, crr.lob, crr.content, crr.content_length, crr.title, crr.description, crr.mime_type, cri.storage_type from evaluation_tasksi et, cr_revisions crr, cr_items cri where task_id = :from_task_id and et.task_id = crr.revision_id and cri.item_id = crr.item_id select evaluation__new_item ( :item_id, --item_id :item_name, null, --locale :creation_user, :to_package_id, current_timestamp, :creation_ip, :task_name, :description, :mime_type, --mime_type null, --nls_language null, --text :storage_type, --storage_type 'content_item', -- item_subtype 'evaluation_tasks' -- content_type ); select evaluation__new_task ( :item_id, :revision_id, :task_name, :number_of_members, :to_grade_item_id, :description, :weight, :due_date, :late_submit_p, :online_p, :requires_grade_p, 'evaluation_tasks', now(), --creation date :creation_user, :creation_ip, :title, now(), --publish date null, -- nls_language :mime_type --mime_type ) update cr_revisions set content = :content, content_length = :content_length, lob = :lob where revision_id = :revision_id select evaluation__new_item ( :item_id, --item_id :item_name, null, --locale :creation_user, :package_id, current_timestamp, :creation_ip, :name, :description, :mime_type, --mime_type null, --nls_language null, --text :storage_type, --storage_type 'content_item', -- item_subtype 'evaluation_tasks' -- content_type ); select evaluation__new_task ( :item_id, :revision_id, :name, :number_of_members, :grade_item_id, :description, :weight, :due_date, :late_submit_p, :online_p, :requires_grade_p, :estimated_time, 'evaluation_tasks', now(), --creation date :creation_user, :creation_ip, :title, now(), --publish date null, -- nls_language :mime_type --mime_type ); update cr_items set name = :item_name, storage_type = :storage_type where item_id = :item_id select evaluation__new_item ( :item_id, --item_id :item_name, null, --locale :creation_user, :package_id, current_timestamp, :creation_ip, :title, 'task solution', :mime_type, --mime_type null, --nls_language null, --text :storage_type, --storage_type 'content_item', -- item_subtype 'evaluation_tasks_sols' -- content_type ); select evaluation__new_task_sol ( :item_id, :revision_id, :task_item_id, 'evaluation_tasks_sols', :creation_date, --creation date :creation_user, :creation_ip, :title, :publish_date, --publish date null, -- nls_language :mime_type --mime_type ); update cr_items set name = :item_name, storage_type = :storage_type where item_id = :item_id select evaluation__new_item ( :item_id, --item_id :item_name, null, --locale :creation_user, :package_id, current_timestamp, :creation_ip, :title, 'evaluation answer', :mime_type, --mime_type null, --nls_language null, --text :storage_type, --storage_type 'content_item', -- item_subtype 'evaluation_answers' -- content_type ); select evaluation__new_answer ( :item_id, :revision_id, :task_item_id, :party_id, 'evaluation_answers', :creation_date, --creation date :creation_user, :creation_ip, :title, :publish_date, --publish date null, -- nls_language :mime_type, --mime_type :comment ); update cr_items set name = :item_name, storage_type = :storage_type where item_id = :item_id select evaluation__new_item ( :item_id, --item_id :item_name, null, --locale :creation_user, :package_id, current_timestamp, :creation_ip, :title, 'student evaluation', :mime_type, --mime_type null, --nls_language null, --text :storage_type, --storage_type 'content_item', -- item_subtype 'evaluation_student_evals' -- content_type ); select evaluation__new_student_eval ( :item_id, :revision_id, :task_item_id, :party_id, :grade, :show_student_p, :description, 'evaluation_student_evals', :creation_date, --creation date :creation_user, :creation_ip, :item_name, --title :publish_date, --publish date null, -- nls_language :mime_type --mime_type ); select evaluation__new_evaluation_task_group ( :group_id, :group_name, 'closed', :creation_date, :creation_user, :creation_ip, :context, :task_item_id ); select acs_group__name(:group_id) as group_name select evaluation__new_item ( :item_id, --item_id :item_name, null, --locale :creation_user, :package_id, current_timestamp, :creation_ip, :title, 'grades sheet', :mime_type, --mime_type null, --nls_language null, --text :storage_type, --storage_type 'content_item', -- item_subtype 'evaluation_grades_sheets' -- content_type ); select evaluation__new_grades_sheet ( :item_id, :revision_id, :task_item_id, 'evaluation_grades_sheets', :creation_date, --creation date :creation_user, :creation_ip, :title, :publish_date, --publish date null, -- nls_language :mime_type --mime_type ); select eg.grade_id from evaluation_tasks est, evaluation_grades eg, cr_items cri where est.task_id = :task_id and est.grade_item_id = eg.grade_item_id and cri.live_revision = eg.grade_id select cu.person_id as party_id, cu.last_name||' - '||cu.first_names as party_name, round(ese.grade,2) as grade, ese.description as comments from cc_users cu left outer join evaluation_student_evalsi ese on (ese.party_id = cu.person_id and ese.task_item_id = :task_item_id and content_revision__is_live(ese.evaluation_id) = true) select p.person_id as party_id, p.last_name||' - '||p.first_names as party_name, ese.grade, ese.description as comments from registered_users ru, dotlrn_member_rels_approved app, persons p left outer join evaluation_student_evalsi ese on (ese.party_id = p.person_id and ese.task_item_id = :task_item_id and content_revision__is_live(ese.evaluation_id) = true) where app.community_id = :community_id and app.user_id = ru.user_id and app.user_id = p.person_id and app.role = 'student' select et.task_name, et.number_of_members, et.task_item_id from evaluation_tasks et where et.task_id = :task_id select etg.group_id as party_id, g.group_name as party_name, grade, ese.description as comments from groups g, evaluation_task_groups etg left outer join evaluation_student_evalsi ese on (ese.party_id = etg.group_id and ese.task_item_id = :task_item_id and content_revision__is_live(ese.evaluation_id) = true) where etg.task_item_id = :task_item_id and etg.group_id = g.group_id $sql_query select eg.grade_name, et.task_name from evaluation_grades eg, evaluation_tasks et, cr_items cri where et.task_id = :task_id and et.grade_item_id = eg.grade_item_id and cri.live_revision = eg.grade_id select description as edit_reason, grade as current_grade, evaluation__party_name(party_id,:task_id) as party_name from evaluation_student_evalsi where evaluation_id = :evaluation_id select evaluation__new_folder ( 'evaluation_grades_'||:package_id, 'evaluation_grades_'||:package_id, 'Evaluation grades folder', null, 'evaluation_grades' ); select evaluation__new_folder ( 'evaluation_tasks_'||:package_id, 'evaluation_tasks_'||:package_id, 'Evaluation tasks folder', null, 'evaluation_tasks' ); select evaluation__new_folder ( 'evaluation_tasks_sols_'||:package_id, 'evaluation_tasks_sols_'||:package_id, 'Evaluation tasks solutions folder', null, 'evaluation_tasks_sols' ); select evaluation__new_folder ( 'evaluation_answers_'||:package_id, 'evaluation_answers_'||:package_id, 'Evaluation answers folder', null, 'evaluation_answers' ); select evaluation__new_folder ( 'evaluation_grades_sheets_'||:package_id, 'evaluation_grades_sheets_'||:package_id, 'Grades sheets folder', null, 'evaluation_grades_sheets' ); select evaluation__new_folder ( 'evaluation_student_evals_'||:package_id, 'evaluation_student_evals_'||:package_id, 'Evaluation student evaluations folder', null, 'evaluation_student_evals' ); select evaluation__new_item ( :exams_item_id, --item_id :exams_item_name, null, :creation_user, :package_id, current_timestamp, :creation_ip, :exams_name, :exams_desc, 'text/plain', null, null, 'text', 'content_item', -- item_subtype 'evaluation_grades' -- content_type ); select evaluation__new_grade ( :exams_item_id, :exams_revision_id, :exams_singular_name, :exams_name, 20, 'evaluation_grades', now(), --creation date :creation_user, :creation_ip, :exams_revision_name, :exams_desc, now(), --publish date null, --nls_language 'text/plain' --mime_type ); select content_item__set_live_revision ( :exams_revision_id ); select evaluation__new_item ( :projects_item_id, --item_id :projects_item_name, null, :creation_user, :package_id, current_timestamp, :creation_ip, :projects_name, :projects_desc, 'text/plain', null, null, 'text', 'content_item', -- item_subtype 'evaluation_grades' -- content_type ); select evaluation__new_grade ( :projects_item_id, :projects_revision_id, :projects_singular_name, :projects_name, 20, 'evaluation_grades', now(), --creation date :creation_user, :creation_ip, :projects_revision_name, :projects_desc, now(), --publish date null, --nls_language 'text/plain' --mime_type ); select content_item__set_live_revision ( :projects_revision_id ); select evaluation__new_item ( :tasks_item_id, --item_id :tasks_item_name, null, :creation_user, :package_id, current_timestamp, :creation_ip, :tasks_name, :tasks_desc, 'text/plain', null, null, 'text', 'content_item', -- item_subtype 'evaluation_grades' -- content_type ); select evaluation__new_grade ( :tasks_item_id, :tasks_revision_id, :tasks_singular_name, :tasks_name, 20, 'evaluation_grades', now(), --creation date :creation_user, :creation_ip, :tasks_revision_name, :tasks_desc, now(), --publish date null, --nls_language 'text/plain' --mime_type ); select content_item__set_live_revision ( :tasks_revision_id ); select evaluation__delete_contents ( :package_id ); select evaluation__delete_folder ( :ev_grades_sheets_fid, 'evaluation_grades_sheets' ); select evaluation__delete_folder ( :ev_grades_fid, 'evaluation_grades' ); select evaluation__delete_folder ( :ev_tasks_fid, 'evaluation_tasks' ); select evaluation__delete_folder ( :ev_tasks_sols_fid, 'evaluation_tasks_sols' ); select evaluation__delete_folder ( :ev_answers_fid, 'evaluation_answers' ); select evaluation__delete_folder ( :ev_student_evals_fid, 'evaluation_student_evals' ); select evaluation__party_name(ea.party_id, et.task_id) as party_name, crr.title as answer_title, crr.revision_id, crr.content as cr_file_name, cri.storage_type, cri.storage_area_key as cr_path from evaluation_answersi ea, cr_revisions crr, evaluation_tasks et, cr_items cri, cr_items cri2 where ea.task_item_id = et.task_item_id and ea.answer_item_id = cri.item_id and crr.revision_id = ea.answer_id and et.task_id = :task_id and ea.data is not null and cri2.live_revision = ea.answer_id and not exists (select 1 from evaluation_student_evals ese, cr_items cri3 where ese.party_id = ea.party_id and ese.task_item_id = et.task_item_id and cri3.live_revision = ese.evaluation_id) select content_revision__get_content(:revision_id) select lob from cr_revisions where revision_id = :revision_id select evaluation__new_item ( :journals_item_id, --item_id :journals_item_name, null, :creation_user, :package_id, current_timestamp, :creation_ip, :journals_name, :journals_desc, 'text/plain', null, null, 'text', 'content_item', -- item_subtype 'evaluation_grades' -- content_type ); select evaluation__new_grade ( :journals_item_id, :journals_revision_id, :journals_singular_name, :journals_name, 20, 'evaluation_grades', now(), --creation date :creation_user, :creation_ip, :journals_revision_name, :journals_desc, now(), --publish date null, --nls_language 'text/plain' --mime_type ); select content_item__set_live_revision ( :journals_revision_id ); select evaluation__new_item ( :final_projects_item_id, --item_id :final_projects_item_name, null, :creation_user, :package_id, current_timestamp, :creation_ip, :final_projects_name, :final_projects_desc, 'text/plain', null, null, 'text', 'content_item', -- item_subtype 'evaluation_grades' -- content_type ); select evaluation__new_grade ( :final_projects_item_id, :final_projects_revision_id, :final_projects_singular_name, :final_projects_name, 10, 'evaluation_grades', now(), --creation date :creation_user, :creation_ip, :final_projects_revision_name, :final_projects_desc, now(), --publish date null, --nls_language 'text/plain' --mime_type ); select content_item__set_live_revision ( :final_projects_revision_id ); select evaluation__new_item ( :activities_item_id, --item_id :activities_item_name, null, :creation_user, :package_id, current_timestamp, :creation_ip, :activities_name, :activities_desc, 'text/plain', null, null, 'text', 'content_item', -- item_subtype 'evaluation_grades' -- content_type ); select evaluation__new_grade ( :activities_item_id, :activities_revision_id, :activities_singular_name, :activities_name, 10, 'evaluation_grades', now(), --creation date :creation_user, :creation_ip, :activities_revision_name, :activities_desc, now(), --publish date null, --nls_language 'text/plain' --mime_type ); select content_item__set_live_revision ( :activities_revision_id ); select evaluation__new_folder ( 'evaluation_grades_'||:package_id, 'evaluation_grades_'||:package_id, 'Evaluation grades folder', null, 'evaluation_grades' ); select evaluation__new_folder ( 'evaluation_tasks_'||:package_id, 'evaluation_tasks_'||:package_id, 'Evaluation tasks folder', null, 'evaluation_tasks' ); select evaluation__new_folder ( 'evaluation_tasks_sols_'||:package_id, 'evaluation_tasks_sols_'||:package_id, 'Evaluation tasks solutions folder', null, 'evaluation_tasks_sols' ); select evaluation__new_folder ( 'evaluation_answers_'||:package_id, 'evaluation_answers_'||:package_id, 'Evaluation answers folder', null, 'evaluation_answers' ); select evaluation__new_folder ( 'evaluation_grades_sheets_'||:package_id, 'evaluation_grades_sheets_'||:package_id, 'Grades sheets folder', null, 'evaluation_grades_sheets' ); select evaluation__new_folder ( 'evaluation_student_evals_'||:package_id, 'evaluation_student_evals_'||:package_id, 'Evaluation student evaluations folder', null, 'evaluation_student_evals' ); select evaluation__new_item ( :journals_item_id, --item_id :journals_item_name, null, :creation_user, :package_id, current_timestamp, :creation_ip, :journals_name, :journals_desc, 'text/plain', null, null, 'text', 'content_item', -- item_subtype 'evaluation_grades' -- content_type ); select evaluation__new_grade ( :journals_item_id, :journals_revision_id, :journals_singular_name, :journals_name, 0, 'evaluation_grades', now(), --creation date :creation_user, :creation_ip, :journals_revision_name, :journals_desc, now(), --publish date null, --nls_language 'text/plain' --mime_type ); select content_item__set_live_revision ( :journals_revision_id ); select evaluation__new_item ( :final_projects_item_id, --item_id :final_projects_item_name, null, :creation_user, :package_id, current_timestamp, :creation_ip, :final_projects_name, :final_projects_desc, 'text/plain', null, null, 'text', 'content_item', -- item_subtype 'evaluation_grades' -- content_type ); select evaluation__new_grade ( :final_projects_item_id, :final_projects_revision_id, :final_projects_singular_name, :final_projects_name, 0, 'evaluation_grades', now(), --creation date :creation_user, :creation_ip, :final_projects_revision_name, :final_projects_desc, now(), --publish date null, --nls_language 'text/plain' --mime_type ); select content_item__set_live_revision ( :final_projects_revision_id ); select evaluation__new_item ( :activities_item_id, --item_id :activities_item_name, null, :creation_user, :package_id, current_timestamp, :creation_ip, :activities_name, :activities_desc, 'text/plain', null, null, 'text', 'content_item', -- item_subtype 'evaluation_grades' -- content_type ); select evaluation__new_grade ( :activities_item_id, :activities_revision_id, :activities_singular_name, :activities_name, 0, 'evaluation_grades', now(), --creation date :creation_user, :creation_ip, :activities_revision_name, :activities_desc, now(), --publish date null, --nls_language 'text/plain' --mime_type ); select content_item__set_live_revision ( :activities_revision_id ); select task_id from evaluation_tasks where task_id=:task_id and due_date is null select evaluation__new_item ( :item_id, --item_id :item_name, null, :creation_user, :package_id, now(), :creation_ip, :name, :description, 'text/plain', null, null, 'text', 'content_item', -- item_subtype 'evaluation_grades' -- content_type ); select evaluation__new_grade ( :item_id, :revision_id, :name, :plural_name, :weight, 'evaluation_grades', now(), --creation date :creation_user, :creation_ip, :revision_name, :description, now(), --publish date null, --nls_language 'text/plain' --mime_type );