USP_DATALIST_CONSTITUENTPROXIMITYRADIUSKM

CREATE procedure dbo.USP_DATALIST_CONSTITUENTPROXIMITYRADIUSKM
(
  @LATITUDE decimal(16,12),
  @LONGITUDE decimal(16,12),
  @DISTANCEKILOMETERS int,
  @MAXROWS integer = 501,
  @CURRENTAPPUSERID uniqueidentifier = null
)
as
  set nocount on;

  declare @ISSYSADMIN bit = dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID);

  -- 1 degree is approximately equal to 112 km on the surface of a sphere.

  declare @DISTANCEDEGREES decimal(16,12) = @DISTANCEKILOMETERS / 112.0;

  declare @MIN_LATITUDE decimal(16,12) = @LATITUDE - @DISTANCEDEGREES;
  declare @MAX_LATITUDE decimal(16,12) = @LATITUDE + @DISTANCEDEGREES;
  declare @MIN_LONGITUDE decimal(16,12) = @LONGITUDE - @DISTANCEDEGREES;
  declare @MAX_LONGITUDE decimal(16,12) = @LONGITUDE + @DISTANCEDEGREES;

  declare @CONSTITUENTSWITHINRANGE table
  (
    ID uniqueidentifier,
    LATITUDE decimal(16,12),
    LONGITUDE decimal(16,12),
    DISTANCE decimal(16,12),
    HASPERMISSIONS int
  );

  -- Collect information

  if @ISSYSADMIN = 1
  begin
    insert into @CONSTITUENTSWITHINRANGE
    select top(@MAXROWS)
      CONSTITUENT.ID,
      coalesce(ADDRESSCOORDINATES.LATITUDE, POSTCODECOORDINATES.LATITUDE),
      coalesce(ADDRESSCOORDINATES.LONGITUDE, POSTCODECOORDINATES.LONGITUDE),
      dbo.UFN_COORDINATES_GETDISTANCEINKILOMETERS(@LATITUDE, @LONGITUDE,
        coalesce(ADDRESSCOORDINATES.LATITUDE, POSTCODECOORDINATES.LATITUDE),
        coalesce(ADDRESSCOORDINATES.LONGITUDE, POSTCODECOORDINATES.LONGITUDE)) as DISTANCE,
      1 as HASPERMISSIONS
    from dbo.CONSTITUENT
      inner join dbo.ADDRESS on CONSTITUENT.ID = [ADDRESS].CONSTITUENTID and ADDRESS.ISPRIMARY = 1
      left join dbo.POSTCODECOORDINATES on ADDRESS.POSTCODE = POSTCODECOORDINATES.POSTCODE
      left join dbo.ADDRESSCOORDINATES on ADDRESS.ID = ADDRESSCOORDINATES.ADDRESSID
    where
      coalesce(ADDRESSCOORDINATES.LATITUDE, POSTCODECOORDINATES.LATITUDE) between @MIN_LATITUDE and @MAX_LATITUDE and
      coalesce(ADDRESSCOORDINATES.LONGITUDE, POSTCODECOORDINATES.LONGITUDE) between @MIN_LONGITUDE and @MAX_LONGITUDE
    order by
      -- Prioritize constituents closer to the target location. Being further from the mark increases

      -- this number, thus lowering the constituent on the result list.

      abs(coalesce(ADDRESSCOORDINATES.LATITUDE, POSTCODECOORDINATES.LATITUDE) - @LATITUDE) +
      abs(coalesce(ADDRESSCOORDINATES.LONGITUDE, POSTCODECOORDINATES.LONGITUDE) - @LONGITUDE);
  end
  else
  begin
    declare @SITEREQUIREDFORUSER bit = dbo.UFN_SITEREQUIREDFORUSER(@CURRENTAPPUSERID);

    if @SITEREQUIREDFORUSER = 1
    begin
      declare @USERSITES table (SITEID uniqueidentifier);

      insert into @USERSITES
      select SITEID from dbo.UFN_SITESFORUSER(@CURRENTAPPUSERID);

      insert into @CONSTITUENTSWITHINRANGE
      select top(@MAXROWS)
        CONSTITUENT.ID,
        coalesce(ADDRESSCOORDINATES.LATITUDE, POSTCODECOORDINATES.LATITUDE),
        coalesce(ADDRESSCOORDINATES.LONGITUDE, POSTCODECOORDINATES.LONGITUDE),
        dbo.UFN_COORDINATES_GETDISTANCEINKILOMETERS(@LATITUDE, @LONGITUDE,
          coalesce(ADDRESSCOORDINATES.LATITUDE, POSTCODECOORDINATES.LATITUDE),
          coalesce(ADDRESSCOORDINATES.LONGITUDE, POSTCODECOORDINATES.LONGITUDE)) as DISTANCE,
        dbo.UFN_SECURITY_APPUSER_GRANTED_DATALIST_FORCONSTIT(@CURRENTAPPUSERID, 'e69c1738-3019-4828-9b2c-d13406bb7996', CONSTITUENT.ID) as HASPERMISSIONS
      from dbo.CONSTITUENT
        inner join dbo.ADDRESS on CONSTITUENT.ID = [ADDRESS].CONSTITUENTID and ADDRESS.ISPRIMARY = 1
        left join dbo.POSTCODECOORDINATES on ADDRESS.POSTCODE = POSTCODECOORDINATES.POSTCODE
        left join dbo.ADDRESSCOORDINATES on ADDRESS.ID = ADDRESSCOORDINATES.ADDRESSID
      where
        coalesce(ADDRESSCOORDINATES.LATITUDE, POSTCODECOORDINATES.LATITUDE) between @MIN_LATITUDE and @MAX_LATITUDE and
        coalesce(ADDRESSCOORDINATES.LONGITUDE, POSTCODECOORDINATES.LONGITUDE) between @MIN_LONGITUDE and @MAX_LONGITUDE and
        (
              exists
              (
                select 1
                from dbo.UFN_SITEID_MAPFROM_CONSTITUENTID(CONSTITUENT.ID) CONSTITSITES
                inner join @USERSITES USERSITES on CONSTITSITES.SITEID = USERSITES.SITEID
              )
        )
      order by
        -- Prioritize constituents closer to the target location. Being further from the mark increases

        -- this number, thus lowering the constituent on the result list.

        abs(coalesce(ADDRESSCOORDINATES.LATITUDE, POSTCODECOORDINATES.LATITUDE) - @LATITUDE) +
        abs(coalesce(ADDRESSCOORDINATES.LONGITUDE, POSTCODECOORDINATES.LONGITUDE) - @LONGITUDE);
    end
    else
    begin
      insert into @CONSTITUENTSWITHINRANGE
      select top(@MAXROWS)
        CONSTITUENT.ID,
        coalesce(ADDRESSCOORDINATES.LATITUDE, POSTCODECOORDINATES.LATITUDE),
        coalesce(ADDRESSCOORDINATES.LONGITUDE, POSTCODECOORDINATES.LONGITUDE),
        dbo.UFN_COORDINATES_GETDISTANCEINKILOMETERS(@LATITUDE, @LONGITUDE,
          coalesce(ADDRESSCOORDINATES.LATITUDE, POSTCODECOORDINATES.LATITUDE),
          coalesce(ADDRESSCOORDINATES.LONGITUDE, POSTCODECOORDINATES.LONGITUDE)) as DISTANCE,
        dbo.UFN_SECURITY_APPUSER_GRANTED_DATALIST_FORCONSTIT(@CURRENTAPPUSERID, 'e69c1738-3019-4828-9b2c-d13406bb7996', CONSTITUENT.ID) as HASPEMISSIONS
      from dbo.CONSTITUENT
        inner join dbo.ADDRESS on CONSTITUENT.ID = [ADDRESS].CONSTITUENTID and ADDRESS.ISPRIMARY = 1
        left join dbo.POSTCODECOORDINATES on ADDRESS.POSTCODE = POSTCODECOORDINATES.POSTCODE
        left join dbo.ADDRESSCOORDINATES on ADDRESS.ID = ADDRESSCOORDINATES.ADDRESSID
      where
        coalesce(ADDRESSCOORDINATES.LATITUDE, POSTCODECOORDINATES.LATITUDE) between @MIN_LATITUDE and @MAX_LATITUDE and
        coalesce(ADDRESSCOORDINATES.LONGITUDE, POSTCODECOORDINATES.LONGITUDE) between @MIN_LONGITUDE and @MAX_LONGITUDE
      order by
        -- Prioritize constituents closer to the target location. Being further from the mark increases

        -- this number, thus lowering the constituent on the result list.

        abs(coalesce(ADDRESSCOORDINATES.LATITUDE, POSTCODECOORDINATES.LATITUDE) - @LATITUDE) +
        abs(coalesce(ADDRESSCOORDINATES.LONGITUDE, POSTCODECOORDINATES.LONGITUDE) - @LONGITUDE);
    end
  end

  -- Return information

  select
    CONSTITUENT.ID,
    CONSTITUENT.NAME,
    ADDRESS.ID [ADDRESSID],
    ADDRESS.ADDRESSBLOCK,
    ADDRESS.CITY,
    STATE.ABBREVIATION [STATE],
    ADDRESS.POSTCODE,
    COUNTRY.ABBREVIATION [COUNTRY],
    CONSTITUENTSWITHINRANGE.LATITUDE,
    CONSTITUENTSWITHINRANGE.LONGITUDE,
    cast(CONSTITUENTSWITHINRANGE.DISTANCE as int) DISTANCEKILOMETERS,
    null as GROUPID,
    null as GROUPNAME,
    dbo.UFN_PHONE_GETINTERNATIONALNUMBER(PHONE.COUNTRYID, PHONE.NUMBER) as PHONENUMBER,
    EMAILADDRESS.EMAILADDRESS
  from @CONSTITUENTSWITHINRANGE CONSTITUENTSWITHINRANGE
  inner join dbo.CONSTITUENT on CONSTITUENTSWITHINRANGE.ID = CONSTITUENT.ID
  inner join dbo.ADDRESS on CONSTITUENT.ID = [ADDRESS].CONSTITUENTID and ADDRESS.ISPRIMARY = 1
  left join dbo.PHONE on CONSTITUENT.ID = PHONE.CONSTITUENTID and PHONE.ISPRIMARY = 1
  left join dbo.EMAILADDRESS on CONSTITUENT.ID = EMAILADDRESS.CONSTITUENTID and EMAILADDRESS.ISPRIMARY = 1
  left join dbo.POSTCODECOORDINATES on ADDRESS.POSTCODE = POSTCODECOORDINATES.POSTCODE
  left join dbo.ADDRESSCOORDINATES on ADDRESS.ID = ADDRESSCOORDINATES.ADDRESSID
  left join dbo.STATE on [STATE].ID = ADDRESS.STATEID
  left join dbo.COUNTRY on COUNTRY.ID = ADDRESS.COUNTRYID
  where CONSTITUENTSWITHINRANGE.DISTANCE <= @DISTANCEKILOMETERS and CONSTITUENTSWITHINRANGE.HASPERMISSIONS = 1
  order by CONSTITUENTSWITHINRANGE.DISTANCE;