ad_user_class_query selectionWhat it does:
Takes an ns_set of key/value pairs and produces a query for the class of users specified (one user per row returned).Defined in: /web/philip/packages/acs-core/admin-procs.tcl
Source code:
# we might need this set where_clauses [list] set join_clauses [list] set group_clauses [list] set having_clauses [list] set tables [list users] # because we named our arg "selection", we can use this magic # utility procedure to set everything as a local var set_variables_after_query # if we are using a user_class, just get the info if { [info exists count_only_p] && $count_only_p } { set select_list "count(users.user_id)" } else { # Get all the non-LOB columns. set user_columns [list] set db [ns_db gethandle subquery] foreach column [GetColumnNames $db "users"] { if { $column != "portrait" && $column != "portrait_thumbnail" } { lappend user_columns "users.$column" } } ns_db releasehandle $db set select_list [join $user_columns ",\n "] } if { [info exists include_contact_p] && $include_contact_p} { append select_list ",\n user_contact_summary(users.user_id) as contact_summary" } if { [info exists include_demographics_p] && $include_demographics_p} { append select_list ",\n user_demographics_summary(users.user_id) as demographics_summary" } if { [info exists user_class_id] && ![empty_string_p $user_class_id] } { set db [ns_db gethandle subquery] set sql_post_select [database_to_tcl_string $db "select sql_post_select from user_classes where user_class_id = $user_class_id"] ns_db releasehandle $db return "select $select_list\n$sql_post_select" } if { [info exists sql_post_select] && ![empty_string_p $sql_post_select] } { return "select $select_list\n$sql_post_select" } foreach criteria [ad_user_class_parameters] { if { [info exists $criteria] && ![empty_string_p [set $criteria]] } { switch $criteria { "category_id" { if {[lsearch $tables "users_interests"] == -1 } { lappend tables "users_interests" lappend join_clauses "users.user_id = users_interests.user_id" } lappend where_clauses "users_interests.category_id = $category_id" } "country_code" { if {[lsearch $tables "users_contact"] == -1 } { lappend tables "users_contact" lappend join_clauses "users.user_id = users_contact.user_id" } lappend where_clauses "users_contact.ha_country_code = '$country_code'" } "usps_abbrev" { if {[lsearch $tables "users_contact"] == -1 } { lappend tables "users_contact" lappend join_clauses "users.user_id = users_contact.user_id" } lappend where_clauses "(users_contact.ha_state = '$usps_abbrev' and (users_contact.ha_country_code is null or users_contact.ha_country_code = 'us'))" } "intranet_user_p" { if {$intranet_user_p == "t" && [lsearch $tables "intranet_users"] == -1 } { lappend tables "intranet_users" lappend join_clauses "users.user_id = intranet_users.user_id" } } "group_id" { #if {[lsearch $tables "users_group_map"] == -1 } { #lappend tables "user_group_map" #lappend join_clauses "users.user_id = user_group_map.user_id" #} #lappend where_clauses "user_group_map.group_id = $group_id" lappend where_clauses "ad_group_member_p(users.user_id, $group_id) = 't'" } "last_name_starts_with" { lappend where_clauses "upper(users.last_name) like upper('[DoubleApos $last_name_starts_with]%')" } "email_starts_with" { lappend where_clauses "upper(users.email) like upper('[DoubleApos $email_starts_with]%')" } "expensive" { if { [info exists count_only_p] && $count_only_p } { lappend where_clauses "[ad_parameter ExpensiveThreshold "member-value"] < (select sum(amount) from users_charges where users_charges.user_id = users.user_id)" } else { if {[lsearch $tables "user_charges"] == -1 } { lappend tables "users_charges" lappend join_clauses "users.user_id = users_charges.user_id" } # we are going to be selecting users.* in general, so # we must group by all the columns in users (can't # GROUP BY USERS.* in Oracle, sadly) set db [ns_db gethandle subquery] foreach column [GetColumnNames $db "users"] { # can't group by a BLOB column. if { $column != "portrait" && $column != "portrait_thumbnail" } { lappend group_clauses "users.$column" } } ns_db releasehandle $db lappend having_clauses "sum(users_charges.amount) > [ad_parameter ExpensiveThreshold "member-value"]" # only the ones where they haven't paid lappend where_clauses "users_charges.order_id is null" } } "user_state" { lappend where_clauses "users.user_state = '$user_state'" } "sex" { if {[lsearch $tables "users_demographics"] == -1 } { lappend tables "users_demographics" lappend join_clauses "users.user_id = users_demographics.user_id" } lappend where_clauses "users_demographics.sex = '$sex'" } "age_below_years" { if {[lsearch $tables "users_demographics"] == -1 } { lappend tables "users_demographics" lappend join_clauses "users.user_id = users_demographics.user_id" } lappend where_clauses "users_demographics.birthdate > sysdate - ($age_below_years * 365.25)" } "age_above_years" { if {[lsearch $tables "users_demographics"] == -1 } { lappend tables "users_demographics" lappend join_clauses "users.user_id = users_demographics.user_id" } lappend where_clauses "users_demographics.birthdate < sysdate - ($age_above_years * 365.25)" } "registration_during_month" { lappend where_clauses "to_char(users.registration_date,'YYYYMM') = '$registration_during_month'" } "registration_before_days" { lappend where_clauses "users.registration_date < sysdate - $registration_before_days" } "registration_after_days" { lappend where_clauses "users.registration_date > sysdate - $registration_after_days" } "registration_after_date" { lappend where_clauses "users.registration_date > '$registration_after_date'" } "last_login_before_days" { lappend where_clauses "users.last_visit < sysdate - $last_login_before_days" } "last_login_after_days" { lappend where_clauses "users.last_visit > sysdate - $last_login_after_days" } "last_login_equals_days" { lappend where_clauses "round(sysdate-last_visit) = $last_login_equals_days" } "number_visits_below" { lappend where_clauses "users.n_sessions < $number_visits_below" } "number_visits_above" { lappend where_clauses "users.n_sessions > $number_visits_above" } "crm_state" { lappend where_clauses "users.crm_state = '$crm_state'" } "curriculum_elements_completed" { lappend where_clauses "$curriculum_elements_completed = (select count(*) from user_curriculum_map ucm where ucm.user_id = users.user_id and ucm.curriculum_element_id in (select curriculum_element_id from curriculum))" } } } } #stuff related to the query itself if { [info exists combine_method] && $combine_method == "or" } { set complete_where [join $where_clauses " or "] } else { set complete_where [join $where_clauses " and "] } if { [info exists include_accumulated_charges_p] && $include_accumulated_charges_p && (![info exists count_only_p] || !$count_only_p) } { # we're looking for expensive users and not just counting them append select_list ", sum(users_charges.amount) as accumulated_charges" } if { [llength $join_clauses] == 0 } { set final_query "select $select_list from [join $tables ", "]" if ![empty_string_p $complete_where] { append final_query "\nwhere $complete_where" } } else { # we're joining at set final_query "select $select_list from [join $tables ", "] where [join $join_clauses "\nand "]" if ![empty_string_p $complete_where] { append final_query "\n and ($complete_where)" } } if { [llength $group_clauses] > 0 } { append final_query "\ngroup by [join $group_clauses ", "]" } if { [llength $having_clauses] > 0 } { append final_query "\nhaving [join $having_clauses " and "]" } return $final_query