<cfoutput>
    <CFIF attributes.user.entity_type eq 'shipper' >
        e.shipper_id = <CFQUERYPARAM value="#attributes.user.entity_id#" >
    <CFELSEIF attributes.user.entity_type eq 'carrier'>
        c.carrier_id = <CFQUERYPARAM value="#attributes.user.entity_id#" >
    <CFELSEIF attributes.user.entity_type eq 'e4score'>
        1
    <CFELSE>
        <CFTHROW message="entity_type unknown">
    </CFIF>

    <CFIF attributes.user.user_filter eq 1 AND attributes.user.shipment_filter neq ''>
        AND e.free_1 IN (<CFQUERYPARAM value="#attributes.user.shipment_filter#" list="true" />)
    </CFIF>

    AND (0


    <CFLOOP array="#attributes.search.groups#" index="search">
        OR (1
        <CFIF structKeyExists(search, 'status') && isnumeric(search.status) >
            <CFIF search.status eq '7.30'>
                AND (
                    st.shipment_status_id > 7.3 AND
                    st.shipment_status_id < 7.6
                )
                <CFELSEIF search.status eq '7.60'>
                AND (
                    st.shipment_status_id > 7.6 AND
                    st.shipment_status_id < 7.7
                )
                <CFELSEIF find('.', search.status) GT 0>
                    AND st.shipment_status_id = <CFQUERYPARAM value="#search.status#">
                <CFELSE>
                    AND (
                        st.shipment_status_id >= <CFQUERYPARAM value="#search.status#"> AND
                        st.shipment_status_id < <CFQUERYPARAM value="#search.status+1#">
                    )
                </CFIF>
        </CFIF>
        <CFIF structKeyExists(search, 'dataType') && structKeyExists(search, 'dataValue') && len(trim(search.dataValue)) GT 0>
            <CFSWITCH expression="#search.dataType#">
                <CFCASE value="bol_number">
                    AND e.bol_number IN ( <CFQUERYPARAM value="#search.dataValue#" list="true" separator=" " cfsqltype="cf_sql_varchar"> )
                                </CFCASE>
                <CFCASE value="wms_bol_number">
                    AND e.wms_bol_number IN ( <CFQUERYPARAM value="#search.dataValue#" list="true" separator=" " cfsqltype="cf_sql_varchar"> )
                                </CFCASE>
                <CFCASE value="pro_number">
                    AND e.pro_number IN ( <CFQUERYPARAM value="#search.dataValue#" list="true" separator=" " cfsqltype="cf_sql_varchar"> )
                                </CFCASE>
                <CFCASE value="tms_shipment_number">
                    AND e.tms_shipment_number IN ( <CFQUERYPARAM value="#search.dataValue#" list="true" separator=" " cfsqltype="cf_sql_varchar"> )
                                </CFCASE>
            </CFSWITCH>
        </CFIF>
        <CFIF structKeyExists(search, 'shipper') && structKeyExists(search.shipper, 'shipper_name')&& len(search.shipper.shipper_name)>
            AND e.shipper_name = <CFQUERYPARAM value="#search.shipper.shipper_name#">
        </CFIF>
        <CFIF structKeyExists(search, 'consignee') && structKeyExists(search.consignee, 'consignee')&& len(search.consignee.consignee)>
            AND e.consignee = <CFQUERYPARAM value="#search.consignee.consignee#">
        </CFIF>
        <CFIF structKeyExists(search, 'carrierAliases') && arrayLen(search.carrierAliases) >
            AND e.carrier_alias IN (<CFQUERYPARAM value="#arrayToList(search.carrierAliases)#" list="true">)
        </CFIF>
        <CFIF structKeyExists(search, 'conditionClass') && len(search.conditionClass)>
            AND e.conformance_rule_id = <CFQUERYPARAM value="#search.conditionClass#">
        </CFIF>
        <CFIF structKeyExists(search, 'shipFrom') && structKeyExists(search.shipFrom, 'shipper_location_id') && len(search.shipFrom.shipper_location_id) >
            AND e.source_location_id = <CFQUERYPARAM value="#search.shipFrom.shipper_location_id#" >
        </CFIF>
        <CFIF structKeyExists(search, 'shipTo') && structKeyExists(search.shipTo, 'shipper_location_id') && len(search.shipTo.shipper_location_id) >
            AND e.dest_location_id = <CFQUERYPARAM value="#search.shipTo.shipper_location_id#" >
        </CFIF>
        <CFIF structKeyExists(search, 'dateType')>
            <CFSWITCH expression="#search.dateType#">
                <CFCASE value="system_create">
                    <CFIF structKeyExists(search, 'start') && isDate(search.start)>
                        AND e.create_date >= <CFQUERYPARAM value="#dateformat(search.start,"yyyy-mm-dd 00:00:00")#" >
                    </CFIF>
                    <CFIF structKeyExists(search, 'end') && isDate(search.end)>
                        AND e.create_date <= <CFQUERYPARAM value="#dateformat(search.end,"yyyy-mm-dd 23:59:59")#" >
                    </CFIF>
                </CFCASE>
                <CFCASE value="pickup">
                    <CFIF structKeyExists(search, 'start') && isDate(search.start)>
                        AND e.pickup_appt_local >= <CFQUERYPARAM value="#dateformat(search.start,"yyyy-mm-dd 00:00:00")#" >
                    </CFIF>
                    <CFIF structKeyExists(search, 'end') && isDate(search.end)>
                        AND e.pickup_appt_local <= <CFQUERYPARAM value="#dateformat(search.end,"yyyy-mm-dd 23:59:59")#" >
                    </CFIF>
                </CFCASE>
                <CFCASE value="delivery">
                    <CFIF structKeyExists(search, 'start') && isDate(search.start)>
                        AND e.delivery_appt_local >= <CFQUERYPARAM value="#dateformat(search.start,"yyyy-mm-dd 00:00:00")#" >
                    </CFIF>
                    <CFIF structKeyExists(search, 'end') && isDate(search.end)>
                        AND e.delivery_appt_local <= <CFQUERYPARAM value="#dateformat(search.end,"yyyy-mm-dd 23:59:59")#" >
                    </CFIF>
                </CFCASE>
            </CFSWITCH>
        </CFIF>
        )
    </CFLOOP>
    )

    <CFIF (!structKeyExists(attributes, 'order') || attributes.order neq false) && structKeyExists(attributes.search, 'order') && arrayContains([
            'pickup_appt_local', 'delivery_appt_local', 'real_pickup_alert', 'real_delivery_alert', 'temp_alert', 'cargo_alert', 'notify',
            'current_status', 'notes_created', 'pickup_detention_risk_alert', 'delivery_detention_risk_alert', 'pickup_imminent_arrival_alert',
            'delivery_imminent_arrival_alert', 'pickup_location_delay_alert', 'delivery_location_delay_alert'], attributes.search.order)>
        <CFSWITCH expression="#attributes.search.order#">
            <CFCASE value="real_pickup_alert,real_delivery_alert,temp_alert,cargo_alert,pickup_detention_risk_alert,delivery_detention_risk_alert,pickup_imminent_arrival_alert,delivery_imminent_arrival_alert,pickup_location_delay_alert,delivery_location_delay_alert" delimiters=",">
                ORDER BY FIELD(`#attributes.search.order#_color`, 'red', 'purple', 'yellow', 'blue', 'white', 'green')
            </CFCASE>
            <CFCASE value="notify">
                ORDER BY (`#attributes.search.order#` IS NULL)
            </CFCASE>
            <CFCASE value="notes_created">
                ORDER BY (`#attributes.search.order#` IS NULL), `#attributes.search.order#`
            </CFCASE>
            <CFCASE value="current_status">
                ORDER BY (st.`shipment_status_id` IS NULL), st.shipment_status_id
            </CFCASE>
            <cfdefaultcase>
                ORDER BY (e.`#attributes.search.order#` IS NULL), e.`#attributes.search.order#`
            </cfdefaultcase>
        </CFSWITCH>
        <CFIF structKeyExists(attributes.search, 'sort_asc') && attributes.search.sort_asc>ASC<CFELSE>DESC</CFIF>
    </CFIF>
</cfoutput>