Hi everyone and thanks for taking the time to read. I was recently made a superuser on a dying site that needs a lot of work. I was able to figure out how to add most of the requested information that I was asked to do. I am down to two problems that I can not figure out on my own. They are:
The board incharge of the site wants me to allow only one username per address(like how there is only one user name allowed on the site). I think this would be possible by using the site's SQl. I have made two fields unit (allowing only a 3 didget number for the numerical of the residence) and address (allowing users to select the street name from a drop down list) that are required on registration. Both fields show up under address in the users module. Does anyone know how I would go about writing this statement/string and adding it to the sql if it is even possible? I also do not have access to the web.config file and don't want to bother the company hosting the site fur us as they host it for free. If It was possible and I were able to add a stament/string to the sql, is it something I could reverse if I messed it up, if not I would contact the person hosting everything for us prior to adding it.
The second thing is I have been using the reports module. I have figured out all of the reports I need to make except for one. I have two different statements That I have not been able to figure out how to join. Both statements work by them self, but I can not seem to join them together proerly. they are as follows:
Statements:
Statment#1
SELECT
u.UserName,
u.FirstName,
u.LastName,
u.Email,
Numerical.PropertyValue AS Numerical,
Street.PropertyValue AS Street,
Telephone.PropertyValue As Telephone,
Committee.PropertyValue AS Committee,
Volunteer.PropertyValue as Volunteer
FROM
dbo.udf_UserProfileField('unit') AS Numerical left OUTER JOIN dbo.vw_Users AS U on Numerical.PortalId = U.PortalId AND Numerical.UserID = U.UserId and Numerical.PropertyValue >= ' ' join
dbo.udf_UserProfileField('street') AS Street ON U.UserId = Street.UserID AND U.PortalId = Street.PortalId and Street.PropertyValue >= ' ' join
dbo.udf_UserProfileField('Telephone') AS Telephone ON U.UserId = Telephone.UserID AND U.PortalId = Telephone.PortalId and Telephone.PropertyValue >= ' ' join
dbo.udf_UserProfileField('Committee') AS Committee ON U.UserId = Committee.UserID AND U.PortalId = Committee.PortalId and Committee.PropertyValue >= ' ' Join
dbo.udf_UserProfileField('Volunteer') AS Volunteer on U.UserId = Volunteer.UserID AND U.PortalId = Volunteer.PortalId
and volunteer.PropertyValue >= ' '
Statement #2
SELECT Authorised from
UserPortals where Authorised='true'
This may seem to juvenile stuff to a lot of you, but I haven't really messed with web design stuff since HTML. I would appreciate any help/advice that you can give to nudge me into the right direction. we are using:
|
DotNetNuke Community Edition |
|
05.06.02 (144) |
and the way I figured out the long statement for user defined fields was by running this script:
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