ticket_build_advs_query

ticket_build_advs_query

build the query from the form

Source code:

 
    set form [ns_conn form]
    if {[empty_string_p $form]} { 
        return {}
    }

    set out {}

    set field(advs_qs) {ti.one_line} 
    set field(advs_tt) {ti.one_line}
    set field(advs_pr) {ti.project_id}
    set field(advs_fa) {ti.domain_id} 
    set field(tc) {ti.posting_time}
    set field(tcl) {ti.closed_date}
    set field(tm) {ti.last_modified}
    set field(td) {ti.deadline}


    # text field queries
    foreach key {advs_qs advs_tt} { 
        set query [ticket_query $form $key $field($key) 1 1]        
        if {![empty_string_p $query]} { 
            lappend out $query
        }
    } 

    set msg_id [join [collapse [split [ns_set get $form {advs_ti}] {, }] {^[0-9]+$}] {,}]

    if {![empty_string_p $msg_id]} {  
        lappend out "ti.msg_id in ($msg_id)"
    }

    # build a list of code based multisearches
    set args [ticket_advs_multi_vars advs {pr fa type status priority severity source cause}]
    ad_page_variables $args
    foreach code $args { 
        if {[empty_string_p [lindex $code 1]]} { 
            lappend fields [lindex $code 0]
        }
        if {![info exists field([lindex $code 0])]} { 
            regsub {advs_} [lindex $code 0] {} code_name
            set field([lindex $code 0]) "ti.${code_name}_id"
        }
    }
    
    foreach key $fields { 
        if {![empty_string_p [set $key]]} { 
            lappend out "$field($key) in ([join [set $key] {,}])"
        }
    }

    # do the date comparisons 
    set sql_comp(le) "<="
    set sql_comp(ge) ">="
    foreach key {tc tm tcl td} { 
        foreach comp {le ge} { 
            set compare [ns_set get $form advs_$key$comp]
            if {![empty_string_p $compare]} { 
                lappend out "trunc($field($key)) $sql_comp($comp) '[DoubleApos $compare]'"
            }
        }
    }

    # the stupid email or last name compare 
    set prefix(advs_tcrat) {users} 
    set prefix(advs_tasto) {assigned_users}
    set prefix(advs_tclby) {closing_users}
    
    foreach key {advs_tcrat advs_tasto advs_tclby} {
        set compare [ns_set get $form $key]
        if {![empty_string_p $compare]} { 
            set compare "'%[DoubleApos [string tolower $compare]]%'"
            lappend out "lower($prefix($key).first_names || ' ' ||$prefix($key).last_name) like $compare or lower($prefix($key).email) like $compare"
        }
    }
    
    if {![empty_string_p $out]} { 
        return "([join $out ")\n   and ("]) "
    } 
    return {}