Hey Luke,

 

I created a role called sales rep, a while back and customers are assigned to sales reps.

 

I want to give sales rep access to login and create orders for their customers from the switch button. Whats the best way to do that?

 

I was thinking of adding one more tab to the my account web part on the front end, where the users will be displayed in the same way as the Admin\users page but only users that are assigned to the logged in sales rep.

 

Is there a better way to do this?

 

Also how do i modify the query for the user list on the admin\users page to include sales rep in there? "SalesRep" is a field in the user table that stores the username of the sales rep assigned to each customer.

 

Thanks

 

 

Luke,

 

Anything on this???

 

I did the step that I mentioned above, create the similar page as a tab in my account in front end. I need to modify the users query to display only customers assigned to the logged in sales rep. Right now its displaying all the customers. Any Ideas?

 

Thanks

 

 

How do you assign the customers to the sale rep? I think you created another table. If so, you can use that table to show the customers who are assigned to the sales rep.

I added one column in DSC_USER table that stores the userID of the SalesRep assigned. I just need to filter the view on users page to display the users where salesrep = logged in username.

 

 

 

Hey Luke,

 

I need to create a new stored procedure similar to DSC_User_Search with one more parameter SalesRep. I am trying to modify this, but its little confusing. Can you please help.

 

Where all do i need to added my parameter for search?? Also @asc is which parameter? What does it do?

 

Thanks

 

 

 

CREATE PROCEDURE [dbo].[DSC_User_Custom_Search]
@keywords NVARCHAR (MAX)=NULL, @from DATETIME='17530101', @to DATETIME='99991231', @rowsPerPage INT=24, @rowIndex INT=0, @salesRep NVARCHAR(265), @orderBy NVARCHAR (100)='created', @asc BIT=0, @totalUsers INT OUTPUT
AS
BEGIN
    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
   
    CREATE TABLE #userAll (rowNumber INT PRIMARY KEY, userId INT, CreateDate DATETIME, Email NVARCHAR(256))

    DECLARE @ASC_DESC NVARCHAR(10)
    IF (@asc = 1)
        SET @ASC_DESC = ''
    ELSE
        SET @ASC_DESC = ' DESC'

    DECLARE @KeywordMatchedUsers NVARCHAR(MAX)
    SET        @KeywordMatchedUsers = ''
    IF ((@keywords IS NOT NULL) AND (LEN(@keywords) > 0))
        BEGIN
            SELECT    @KeywordMatchedUsers = @KeywordMatchedUsers + '
                (SELECT  u.userId
                FROM    users usersToCheck
                JOIN    dbo.DSC_User u
                        ON usersToCheck.userId = u.userId
                WHERE   CONTAINS(u.*,' + QUOTENAME(dbo.DSC_Escape(dbo.DSC_Escape(Value, N'\'), N';'), '''') +  ')
                UNION
                SELECT  u.userId
                FROM    users u
                JOIN    dbo.DSC_Address a
                        ON (a.userId = u.userId) AND (a.isBilling = 1)
                WHERE    CONTAINS(a.*,' + QUOTENAME(dbo.DSC_Escape(dbo.DSC_Escape(Value, N'\'), N';'), '''') +  ')
                UNION
                SELECT  u.userId
                FROM    users u
                JOIN    aspnet_Users aspUser
                        ON (aspUser.LoweredUserName = u.UserName) AND (aspUser.ApplicationId = ''0d663bb4-8150-479b-880d-cca0342f9e35'')
                JOIN    aspnet_Membership aspMembership
                        ON (aspMembership.UserId = aspUser.UserId) AND (aspMembership.ApplicationId = ''0d663bb4-8150-479b-880d-cca0342f9e35'')
                WHERE    CONTAINS(aspMembership.*,' + QUOTENAME(dbo.DSC_Escape(dbo.DSC_Escape(Value, N'\'), N';'), '''') +  '))
                INTERSECT'
            FROM    DSC_Parse_Values(@keywords, DEFAULT)
            SET        @KeywordMatchedUsers = LEFT(@KeywordMatchedUsers, LEN(@KeywordMatchedUsers) - 9) -- remove tailing INTERSECT
        END
    ELSE
        SET @KeywordMatchedUsers = 'SELECT userId FROM users'

    DECLARE @searchStatement NVARCHAR(MAX)
    SET        @searchStatement = '
    ;WITH  users AS
    (
        SELECT    u.userId, u.UserName
        FROM    dbo.DSC_User u
        WHERE    u.userName <> ''_DSCRoot_''
        AND        @from <= u.created
        AND        u.created <= @to
    ),
    keywordMatchedUsers AS
    (' + @KeywordMatchedUsers + '
    ),
    matchedUsers AS
    (
        SELECT    DISTINCT userId
        FROM    keywordMatchedUsers
    )
    INSERT INTO #userAll
    SELECT    ROW_NUMBER() OVER (ORDER BY ' + QUOTENAME(@OrderBy) + @ASC_DESC + ') AS RowNumber, mu.userId, u.created, aspMembership.Email
    FROM    matchedUsers mu
    JOIN    DSC_User u
            ON u.userId = mu.userId
    JOIN    aspnet_Users aspUser
            ON (aspUser.LoweredUserName = u.UserName) AND (aspUser.ApplicationId = ''0d663bb4-8150-479b-880d-cca0342f9e35'')
    JOIN    aspnet_Membership aspMembership
            ON (aspMembership.UserId = aspUser.UserId) AND (aspMembership.ApplicationId = ''0d663bb4-8150-479b-880d-cca0342f9e35'')'

    --PRINT    @searchStatement
    EXECUTE sp_executesql @searchStatement, N'@from DATETIME, @to DATETIME', @from, @to

    SELECT    @totalUsers = COUNT(*)
    FROM    #userAll

    SELECT    *
    INTO    #user
    FROM    #userAll u
    WHERE    u.RowNumber BETWEEN @rowIndex + 1 AND @rowIndex + @rowsPerPage
    ORDER BY u.RowNumber

    DROP TABLE #userAll

    EXEC DSC_User_GetByTempTable

    DROP TABLE #user
END
 

You don't need to touch other parameters. Just add @salesRep to the end and then add it to the where clause of the select statement.