 |
 |
|
|
|
|
 |
|
|
GoMap (free module)
Review and discuss the new FREE GoMaps module, Google Maps and DotNetNuke Integration.
| You are not authorized to post a reply. |
|
| |
|
jgurley
Posts:3
 |
| 06/05/2008 8:05 PM |
 Alert
|
I think I might be overlooking a critical piece of documentation (or alternatively, part of my frontal lobe), but I don't understand how to get the Portal users plotted on a map.
If I choose vwPeopleOnline as the table, I get all the users on the entire host including those not logged in.
If I create a new table, I get nothing. When I create the new table, there is at least one user logged in who has a profile with city/state/country configured, so the wizard indicates everything should be automatic.
Although I'm a super user for the DNN installation, I don't have access to any other components on the host (I don't even know where the host is physically located). There are all sorts of comments about fixing this problem by editing SQL tables, etc. which isn't in something I can do.
What am I missing? |
|
|
|
robert_chumley
Posts:595
|
| 06/09/2008 10:10 AM |
Alert
|
Hello jgurley,
Yeah, users online is not exactly Users Online right now. DNN keeps that information as a logging mechanism, but really is all users in the system, logged in or not. There are some features that Map needs (now DNN Map of course offered from the DNN website). If you are seeing folks from all portals, then yes you may need a way of specifying which portal goes into which map. Currently, the only way to do that is through a trigger on the UserProfile table. Let me know if you need an example.
Thanks, |
|
Robert Chumley
r2integrated (formally bi4ce) |
|
jgurley
Posts:3
|
| 06/09/2008 10:26 AM |
Alert
|
| I at least need an explanation of "trigger". Can i do it from the UI? |
|
|
|
robert_chumley
Posts:595
|
| 06/09/2008 10:36 AM |
Alert
|
You know, database trigger? Here is an example. Remember this gets executed every time someone makes a change to the profile. This did exist in GoMap but does not exist in DNN Map. (Cannot have anything that automaticaly updates the database, rule from the DNN community.)
This does not do exactly what you want, so you will need to tailor it.
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
--* CREATE PROFILE TRIGGER FOR GEOLOOKUP
--* NO LONGER USED
--* CREATE USERPROFILE TRIGGER FOR GEOLOOKUP
ALTER TRIGGER [dbo].[trg_UserProfileMapPoints] ON [dbo].[UserProfile]
AFTER INSERT, UPDATE
AS
-- only update for city, region, or country properties
DECLARE @UserID INT
DECLARE @map_cursor CURSOR
set @map_cursor = CURSOR FOR
SELECT UserID FROM inserted
OPEN @map_cursor
FETCH NEXT
FROM @map_cursor INTO @UserID
WHILE @@FETCH_STATUS = 0
BEGIN
IF EXISTS (SELECT * FROM inserted WHERE PropertyDefinitionID in
(SELECT PropertyDefinitionID FROM dbo.ProfilePropertyDefinition WHERE PropertyName in ('City', 'Region', 'Country','PostalCode'))
and UserID= @UserID
)
BEGIN
IF NOT EXISTS (SELECT TOP 1 * FROM dbo.Map_Points WHERE GUID = @UserID and SourceID = 1)
BEGIN
INSERT INTO dbo.Map_Points (SourceID, GUID, Address, IconIndex, Description, SequenceNumber, SequenceInfo,
ZoomShow, ZoomHide, SummaryCount)
select
1 as SourceID,
@UserID as GUID,
ISNULL(UNIT,'') + ' ' + ISNULL(STREET,'') + ' ' + ISNULL(CITY,'') + ' ' + ISNULL(REGION,'') + ' ' + ISNULL(PostalCode,'')+ ' ' + ISNULL(Country,'') as Address,
0 as IconIndex,
'<strong>' + ISNULL(CompanyName,'') + '</strong><br/><br/><strong>' +
ISNULL(FirstName,'') + ' ' + ISNULL(LastName,'') + '</strong><br/>' +
ISNULL(STREET,'') + '<br/>' + ISNULL(CITY,'') + ', ' + ISNULL(REGION,'') + ' ' + ISNULL(POSTALCODE,'') + '<br/><br/><strong>Phone:</strong> ' + IsNULL(Telephone, ' ')
+ '<br/><strong>Website:</strong> <a href="#" onclick="window.open(''http://' + ISNULL(WEBSITE,'') + ''','''');return false;" >'+ ISNULL(WEBSITE,'') + '</a>' as Description,
0 as SequenceNumber,'' as SequenceInfo, 0 as ZoomShow, 0 as ZoomHide, 1 as SummaryCount
from
(select PropertyValue,
PropertyName
from Users u
join UserProfile p
on u.UserID = p.UserID
join ProfilePropertyDefinition pd on
p.PropertyDefinitionID = pd.PropertyDefinitionID
where pd.PropertyName
in ('Unit','Street','City','Region','PostalCode','Website','CompanyName','Telephone','FirstName','LastName','Country')
and u.UserID = @UserID and pd.PortalID = 0
) as A
pivot
(
max(PropertyValue)
for PropertyName in (Unit,Street,City,Region,PostalCode,Website,CompanyName,Telephone,FirstName,LastName,Country)
) as p
END
ELSE
BEGIN
UPDATE dbo.Map_Points
SET Address = ISNULL(UNIT,'') + ' ' + ISNULL(STREET,'') + ' ' + ISNULL(CITY,'') + ' ' + ISNULL(REGION,'') + ' ' + ISNULL(PostalCode,'')+ ' ' + ISNULL(Country,''),
Description = '<strong>' + ISNULL(CompanyName,'') + '</strong><br/><br/><strong>' +
ISNULL(FirstName,'') + ' ' + ISNULL(LastName,'') + '</strong><br/>' +
ISNULL(STREET,'') + '<br/>' + ISNULL(CITY,'') + ', ' + ISNULL(REGION,'') + ' ' + ISNULL(POSTALCODE,'') + '<br/><br/><strong>Phone:</strong> ' + IsNULL(Telephone, ' ')
+ '<br/><strong>Website:</strong><a href="#" onclick="window.open (''http://' + ISNULL(WEBSITE,'') + ''','''');return false;" >'+ ISNULL(WEBSITE,'') + '</a>'
from
(select PropertyValue,
PropertyName
from Users u
join UserProfile p
on u.UserID = p.UserID
join ProfilePropertyDefinition pd on
p.PropertyDefinitionID = pd.PropertyDefinitionID
where pd.PropertyName
in ('Unit','Street','City','Region','PostalCode','Website','CompanyName','Telephone','FirstName','LastName','Country')
and u.UserID = @UserID and pd.PortalID = 0
) as A
pivot
(
max(PropertyValue)
for PropertyName in (Unit,Street,City,Region,PostalCode,Website,CompanyName,Telephone,FirstName,LastName,Country)
) as p
WHERE GUID = @UserID and SourceID = 1
END
END
FETCH NEXT
FROM @map_cursor INTO @UserID
END
CLOSE @map_cursor
DEALLOCATE @map_cursor
This query sets a new address style to the location descriptions. If you are not using DNN map this will not work, just an FYI.
Thanks, |
|
Robert Chumley
r2integrated (formally bi4ce) |
|
jgurley
Posts:3
|
| 06/15/2008 10:10 AM |
Alert
|
| Sorry, but I don't understand how I tell DNN to do anything like the above. It appears the SQL host menu is just for one-time scripts. |
|
|
|
robert_chumley
Posts:595
|
| 06/16/2008 10:15 AM |
Alert
|
Hello,
This is a script that adds a trigger to your UserProfile table. For a tutorial on Triggers see here
http://doc.ddart.net/mssql/sql70/create_8.htm
Thanks, |
|
Robert Chumley
r2integrated (formally bi4ce) |
|
robert_chumley
Posts:595
|
| 06/16/2008 10:15 AM |
Alert
|
Hello,
This is a script that adds a trigger to your UserProfile table. For a tutorial on Triggers see here
http://doc.ddart.net/mssql/sql70/create_8.htm
Thanks, |
|
Robert Chumley
r2integrated (formally bi4ce) |
|
|
| You are not authorized to post a reply. |
|
|
|
ActiveForums 3.6
|
|
| Latest Post
|
 |
|
At R2integrated (formerly Bi4ce), we take support seriously. That's why we support our customers and DNN community with daily monitoring from our experienced engineering team. We ask that the first step taken is to read the relevant documentation and support forums prior to submitting any questions that may already be available or have been answered. We ask that you review the documentation that we provide for our products before posting a question.
The Forums are for our customers to chat, exchange ideas and strategies, and submit feedback. Please be sure to perform keyword searches for previous related forum responses.
To be helpful when submitting a new item, please include the following:
- DNN Version
- Module Version
- Admin Log Viewer Information
- Environment detail: Operating system, .NET framework version, database and version, IIS version, Browser version (if appropriate)
We always try to respond quickly and monitor the forums daily during business hours (EST). Occasionally, requests for a specific project requirement may not apply for the free support offered. For project specific support please submit via our Information Request form.
Thank you for using our Forums.
Click here to register for the Forums
|
|
|
|
| © 2008 by R2integrated (formerly Bi4ce) | DNN® is a registered trademark of DotNetNuke Corporation
|
 |
|
|
|
 |
|