Index: openacs-4/contrib/packages/photobook/lib/person.tcl =================================================================== RCS file: /usr/local/cvsroot/openacs-4/contrib/packages/photobook/lib/person.tcl,v diff -u -N -r1.1 -r1.2 --- openacs-4/contrib/packages/photobook/lib/person.tcl 3 Nov 2003 20:10:55 -0000 1.1 +++ openacs-4/contrib/packages/photobook/lib/person.tcl 18 Mar 2004 22:45:41 -0000 1.2 @@ -78,28 +78,59 @@ set pre ", " } - db_multirow -append=[expr $i > 0] -extend role phb_all get_user_defaults "select c.user_id as def_user_id, - c.first_names as def_first_name, - c.last_name as def_last_name, - c.email as def_outside, - c.url as user_url, - c.creation_date, - c.member_state, - s.efl, s.alias, s.outside, + # when possible, we want to sort by the users role in the community first, + # then by their name. But this code can be used outside of a community + # as well, so we have to handle that case too, by sorting by name only + set community_id [dotlrn_community::get_community_id] + if { [string length $community_id] > 0 } { + db_multirow -append=[expr $i > 0] -extend role phb_all get_user_defaults "select pe.person_id as def_user_id, + pe.first_names as def_first_name, + pe.last_name as def_last_name, + pa.email as def_outside, + pa.url as user_url, + s.efl, s.alias, s.outside, decode(p.mime_type,'image/jpeg','jpg','image/gif','gif',decode(p.relation_tag,null,'','jpg')) as image_type - FROM cc_users c, sloan_email s, phb_portraits p - WHERE c.user_id in (${user_list}) - and s.user_id(+) = c.user_id - and p.user_id(+) = c.user_id + FROM persons pe, parties pa, sloan_email s, phb_portraits p, dotlrn_member_rels_approved r + WHERE pe.person_id in (${user_list}) + and pe.person_id = pa.party_id + and s.user_id(+) = pe.person_id + and p.user_id(+) = pe.person_id and p.relation_tag(+) = 'portrait_web' - order by upper(c.last_name), upper(c.first_names)" \ + and pe.person_id = r.user_id + and r.community_id = [dotlrn_community::get_community_id] + order by decode(r.role, + 'instructor',1, + 'admin',2, + 'teaching_assistant',3, + 'course_assistant',4, + 'course_admin',5, + 'student',6, + 'member',7), pe.last_name, pe.first_names" \ { set role $user_role($def_user_id) } + } else { + db_multirow -append=[expr $i > 0] -extend role phb_all get_user_defaults "select pe.person_id as def_user_id, + pe.first_names as def_first_name, + pe.last_name as def_last_name, + pa.email as def_outside, + pa.url as user_url, + s.efl, s.alias, s.outside, + decode(p.mime_type,'image/jpeg','jpg','image/gif','gif',decode(p.relation_tag,null,'','jpg')) as image_type + FROM persons pe, parties pa, sloan_email s, phb_portraits p + WHERE pe.person_id in (${user_list}) + and pe.person_id = pa.party_id + and s.user_id(+) = pe.person_id + and p.user_id(+) = pe.person_id + and p.relation_tag(+) = 'portrait_web' + order by pe.person_id, pe.first_names" \ + { + set role $user_role($def_user_id) + } + } } - if {![multirow size phb_all]} { ns_returnnotfound ad_script_abort @@ -126,16 +157,43 @@ # actually pull out the data # +# NOTE - this code was orinally very slow. I made a couple of changes. +# First I rewrote the qery to use, for example, phb_person instead of +# phb_personx. This helped considerably (we were doing full table scans +# before), but it was still too slow. Then I decided that the list of +# item ids should be a bind variable, but you can't use a bind variable +# directly in an IN clause (Oracle sees it as one string). The str2tbl +# function came from Tom Kyte (http://asktom.oracle.com) +# +# The big catch here is that the value passed to str2tbl can't be longer +# than 4000 characters. I did not attempt to fix this properly here; this +# code will probably be rewritten for phase 2 and I've already spent longer +# than I should have getting this to work. The max ids that will fit in 4000 +# chars is actually 500, but I cut it back to 450 to allow for the numbers to +# get longer as the object_ids get larger. This should suffice for now. +# +# I also tried inserting the ids into a temporary table and then having a +# simple select in the IN clause. It worked and looked nicer but took about +# twice as long to run as this does. +# +# Janine Sisk, 2-25-04 + +set max_ids 450 + foreach type [array name ids] { if {[string equal image $type] || ![llength $ids($type)]} { # Do not retrieve images or things with no rows. } else { - for {set i 0} {[llength $ids($type)] > [expr {$i * $max_in_items}]} {incr i} { + # need type name without prefix for id column + set id_col "[string range $type 4 end]_id" + for {set i 0} {[llength $ids($type)] > [expr {$i * $max_ids}]} {incr i} { + set id_list "[join [lrange $ids($type) [expr $i * $max_ids] [expr {($i+1)*$max_ids - 1}]] ","]" db_multirow -append=[expr $i > 0] $type get " - SELECT i.parent_id, r.relation_tag, t.item_id, t.revision_id, t.name, [join [phb::type_attributes $type] ", "] - FROM ${type}x t, cr_items i, cr_child_rels r - WHERE t.item_id in ([join [lrange $ids($type) [expr $i * $max_in_items] [expr {($i+1)*$max_in_items - 1}]] ", "]) - and t.revision_id = i.latest_revision + SELECT i.parent_id, r.relation_tag, rev.item_id, rev.revision_id, i.name, [join [phb::type_attributes $type] ", "] + FROM ${type} t, cr_items i, cr_child_rels r, cr_revisions rev + WHERE rev.item_id in (select * from table ( cast ( str2tbl(:id_list) as myTableType))) + and rev.revision_id = i.latest_revision + and t.${id_col} = rev.revision_id and r.parent_id(+) = i.parent_id and r.child_id(+) = i.item_id" \ { @@ -216,41 +274,66 @@ eval template::multirow extend phb_all [phb::type_attributes phb_person] item_id private_p photobook_user +# NOTE - this code originally walked through phb_all, and started over again +# at the beginning of phb_person each time, looking for a match. This was +# very, very slow. Now I create lists of lists, one from phb_all and one from +# phb_persons, each containing the user_id and it's actual location the array. +# We now walk through the lists at the same time, treating the one from phb_all +# as the master list. If there is a match to phb_person, we process it and +# move down both lists. If not, we process the record and only move down phb_all. +# We can do this because we know that every phb_person will be in phb_all. +# This code is now very fast. +# +# set up the lists of lists. +set phb_all_users [list] +set phb_person_users [list] for {set i 1} {$i <= [multirow size phb_all]} {incr i} { - set found_p 0 - for {set j 1} {$j <= [multirow size phb_person]} {incr j} { - if {[set phb_all:${i}(def_user_id)] == [set phb_person:${j}(user_id)]} { - set found_p 1 - array set phb_all:${i} [array get phb_person:${j}] - set phb_all:${i}(photobook_user) 1 - if {[exists_and_not_null phb_all:${i}(priv_$mode)]} { - set phb_all:${i}(private_p) [expr {[set phb_all:${i}(priv)] || [set phb_all:${i}(priv_$mode)]}] - } else { - set phb_all:${i}(private_p) [set phb_all:${i}(priv)] - } - foreach var {first_name last_name} { - if {![exists_and_not_null phb_all:${i}($var)]} { - set phb_all:${i}($var) [set phb_all:${i}(def_$var)] - } - } - break + lappend phb_all_users [list [set phb_all:${i}(def_user_id)] $i] +} +set phb_all_users [lsort $phb_all_users] +for {set i 1} {$i <= [multirow size phb_person]} {incr i} { + lappend phb_person_users [list [set phb_person:${i}(user_id)] $i] +} +set phb_person_users [lsort $phb_person_users] + +# walk through the list from phb_all +set phb_person_users_index 0 +for {set i 0} {$i < [llength $phb_all_users]} {incr i} { + set phb_all_user_id [lindex [lindex $phb_all_users $i] 0] + set phb_all_index [lindex [lindex $phb_all_users $i] 1] + set phb_person_user_id [lindex [lindex $phb_person_users $phb_person_users_index] 0] + set phb_person_index [lindex [lindex $phb_person_users $phb_person_users_index] 1] + if {$phb_all_user_id == $phb_person_user_id} { + # we found a match + incr phb_person_users_index + array set phb_all:${phb_all_index} [array get phb_person:${phb_person_index}] + set phb_all:${phb_all_index}(photobook_user) 1 + if {[exists_and_not_null phb_all:${phb_all_index}(priv_$mode)]} { + set phb_all:${phb_all_index}(private_p) [expr {[set phb_all:${phb_all_index}(priv)] || [set phb_all:${phb_all_index}(priv_$mode)]}] + } else { + set phb_all:${phb_all_index}(private_p) [set phb_all:${phb_all_index}(priv)] } - } - if {!$found_p} { + foreach var {first_name last_name} { + if {![exists_and_not_null phb_all:${phb_all_index}($var)]} { + set phb_all:${phb_all_index}($var) [set phb_all:${phb_all_index}(def_$var)] + } + } + } else { + # we didn't find a match, so just use the basics (user has not + # entered any profile data yet) foreach var {user_id first_name last_name outside} { - set phb_all:${i}($var) [set phb_all:${i}(def_$var)] + set phb_all:${phb_all_index}($var) [set phb_all:${phb_all_index}(def_$var)] } foreach var {salutation} { - set phb_all:${i}($var) {} + set phb_all:${phb_all_index}($var) {} } foreach var {private_p photobook_user} { - set phb_all:${i}($var) 0 + set phb_all:${phb_all_index}($var) 0 } } } - # Add any synthetic fields... # # @@ -438,14 +521,19 @@ outside_interest_3 outside_interest_3 partner_firstname partner_firstname partner_lastname partner_lastname - past_employers past_employers + past_employer_company_1 past_employer_company_1 + past_employer_citystate_1 past_employer_citystate_1 + past_employer_company_2 past_employer_company_2 + past_employer_citystate_2 past_employer_citystate_2 preferred_graduation preferred_graduation program program salutation salutation status status status_note status_note urop_1 urop_1 + urop_professor_1 urop_professor_1 urop_2 urop_2 + urop_professor_2 urop_professor_2 photobook_user photobook_user children children languages languages @@ -464,7 +552,10 @@ ended "Ended" } phb_span internship { - institution "Company" + institution_position "Position" + institution_company "Company" + institution_city "City" + institution_state "State" } phb_span job_current { institution "Employer" @@ -542,9 +633,9 @@ } if {[string equal $_type phb_span] && [string equal $_relation degree] - && [llength $rows] < 4 + && [llength $rows] < 6 } { - set rows [concat $rows [lrange {0 0 0 0} 0 [expr 3 - [llength $rows]]]] + set rows [concat $rows [lrange {0 0 0 0 0 0} 0 [expr 5 - [llength $rows]]]] } } else { set rows $i @@ -611,7 +702,7 @@ } } if {[string match "10" $phb_all(priv_employment)]} { - if {[lsearch [list past_employers] $_field] > -1} { + if {[lsearch [list past_employer_company_1 past_employer_citystate_1 past_employer_company_2 past_employer_citystate_2] $_field] > -1} { set output "$pre" } }