Branch Locator SQL Script

BMF SQL

<cfquery name="geteverything" datasource="bmf" result="info">
SELECT
  organisation.*,
  address.*
  <cfif radius neq "" AND lat neq "" AND lng neq ""> <!--- they are searching by location --->
  ,
  (
    3959 * acos( 
        cos( 
            radians('#lat#') <!--- passed in latitude from client side script --->
        ) 
        * 
        cos( 
          radians(latitude)
        ) 
        * 
        cos( 
          radians(longitude) - radians('#lng#') <!--- passed in longitude from client side script --->
        ) 
        + 
        sin( 
          radians('#lat#') <!--- passed in latitude from client side script --->
        ) 
        * 
        sin( 
          radians(latitude) 
        ) 
    ) 
  ) AS distance
  </cfif>
FROM
  organisation,
  address
  <cfif activity neq ""> <!--- they are filtering by activity --->
  ,
  activity,
  orgCat
  </cfif>
WHERE
  organisation.source != 'WEBEX'
  AND
  address.addressID = organisation.addressID
  <cfif companyName neq ""> <!--- they are searching by name --->
  AND
  organisation.name LIKE <cfqueryparam cfsqltype="cf_sql_varchar" value="%#companyName#%">
  </cfif>
  <cfif radius neq ""> <!--- they want results within a certain radius --->
  AND
  address.latitude is not null
  </cfif>
  AND
  status IN (<cfqueryparam cfsqltype="cf_sql_varchar" list="true" value="#status#">)
  <cfif activity neq ""><!--- they are filtering by activity --->
  AND
  activity.id IN (<cfqueryparam cfsqltype="cf_sql_varchar" value="#activity#" list="true">)
  AND
  orgCat.activityValue= activity.id
  AND
  organisation.orgNumber = orgCat.orgID
  </cfif>
  <cfif radius neq "" AND lat neq "" AND lng neq ""><!--- they want results within a certain radius --->
  HAVING
  distance < '#radius#'
  </cfif>
  ORDER BY 
  <cfif radius neq "" AND lat neq "" AND lng neq ""><!--- only order by distance if they are searching by location --->
    distance,
  </cfif> 
  name;
</cfquery>