Add any comments or questions regarding the ZLDNN Advanced Biz Map For DotNetNuke tutorial from Issue 61

Hi Lee,

Good work. Do you have a sql script for ZLDNN Advanced Biz Map to show all the registered user location on the map? 'Cause this would be a very common approach that a lot of us would like to show case.

Thanks Alan

You could ask ZLDNN to share the script that they use in their demo site.

I'll also take a shot a creating such a script. I hope that I can get this done today.

Hi Joseph,

I have asked the ZLDNN for the script, and I am still waiting for their response. If you did have a chance to write it that would be greatly appreciated.

Thanks,
Alan

Yes, sorry.  This is slightly more complicated that I first thought, because of the way that DotNetNuke stores profile propertis.  If you understand SQL, this blog entry ought to help you with the basics.

If this is a bit much for you, let me know and I'll give it another shot.

You will also need to give some thought to how much information will you require your subscribers to enter.  A full address?  Or just a city, state, country?

Hi Joseph,

Thanks for the link, I must admit I am struggling a bit to get this to work. Lei from ZLDNN got back to me with the script he used, this was his response:

Hello Alan,

There is no such script but I have a demo script to show user number of each country. You can change it to build the script to show users.

select PropertyValue as [address], PropertyValue + ':' + str(count(*)) as  le,PropertyValue + ':' + str(count(*)) as description,icon = case when count(*)<5 then 'http://demo.zldnn.com/images/icon_users_16px.gif' else 'http://demo.zldnn.com/images/icon_users_32px.gif' end, iconwidth= case when count(*)<5 then 16 else 32 end,iconheight=case when count(*)<5 then 16 else 32 end from {objectQualifier}userprofile as a
inner join {objectQualifier}profilepropertydefinition as b on a.propertydefinitionid=b.propertydefinitionid
where b.portalid=[portalid] and b.PropertyCategory='Address' and b.PropertyName='Country' and not propertyvalue is null and PropertyValue<>'' and PropertyValue<>''
Group by PropertyValue order by count(*) desc

The demo page is here: http://demo.zldnn.com/bizmap/Datafromdatabase.aspx. It shows users of demo.zldnn.com around world.
------------------------------

So If you were willing to assist creating a script to display users then that would be much appreciated.

Thanks,
Alan

Start by creating this function in your DotNetNuke database.  You can paste the script into the Host, SQL window and check "Run as Script" first.



/****** Object:  UserDefinedFunction [dbo].[udf_UserProfileField]    Script Date: 12/07/2010 13:58:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[udf_UserProfileField]
    (
      @PropertyName NVARCHAR(50)
    )
RETURNS @ProfileFieldTable TABLE
    (
      PortalId INT,
      UserID INT,
      PropertyName NVARCHAR(50),
      PropertyValue NVARCHAR(3750)
    )
AS BEGIN
    INSERT  INTO @ProfileFieldTable
            SELECT  PPD.PortalID,
                    UP.UserID,
                    PPD.PropertyName,
                    UP.PropertyValue
            FROM    dbo.Users AS U
                    INNER JOIN dbo.UserProfile AS UP ON U.UserID = UP.UserID
                    INNER JOIN dbo.ProfilePropertyDefinition AS PPD ON UP.PropertyDefinitionID = PPD.PropertyDefinitionID
            WHERE   ( PPD.PropertyName = @PropertyName )
   
    RETURN
   END



Next, your query should be something like this:

SELECT     C.PropertyValue + ' ' + R.PropertyValue + ' ' + Co.PropertyValue as [address]
FROM         dbo.udf_UserProfileField('City') as C

inner join dbo.udf_UserProfileField('Region') as R on C.PortalId=R.PortalID and C.UserId=R.UserId
inner join dbo.udf_UserProfileField('Country') as Co on Co.PortalId=R.PortalID and Co.UserId=R.UserId


Start by running that from the SQL window (NOT as a script) and look at what is returned.  You should have a list of "city region country" for all of the users in your database.  If you want to restrict this to a particular portal (should you have more than one) add:

WHERE C.PortalId=0

Replace the 0 with 1 or 2 or whatever index your portal has.

When you have this ok, you can try pasting that as the query in your map module's configuration.

From the documentation, it would appear that having an "address" is the minimum that the module needs.  The documentation adds more possibilities, including picking the PortalId automatically.

Let me know if you need more.

Hi Joseph,

Thanks soooo much for your help. I really appreciate that.
The stored procedure went well perfectly. And the query for the map module's configuration is also great.

Thanks heaps!
Alan

Hi Joseph,

Thanks soooo much for your help. I really appreciate that.
The stored procedure went well perfectly. And the query for the map module's configuration is also great.

Thanks heaps!
Alan