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