SEARCH FOR CUSTOMER BY NAME IN ADMINISTRATION

I have figured out a way to change the "Email" box in manage customers to a "Name or Email" box. It requires some SQL work.

Step 1.

Run this in your SQL program:

Update Nop_LocaleStringResource
SET ResourceValue = 'Name/Email: '
WHERE LocaleStringResourceID = '8222'

Update Nop_LocaleStringResource
SET ResourceValue = 'The name or email of the customer.'
WHERE LocaleStringResourceID = '8223'

Step 2.

Run this in your SQL program:


USE [*YOURDATABASENAME*]
GO
/****** Object:  StoredProcedure [dbo].[Nop_CustomerLoadAll]    Script Date: 09/09/2010 08:59:32 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[Nop_CustomerLoadAll]
(
  @StartTime        datetime = NULL,
  @EndTime        datetime = NULL,
  @Email          nvarchar(200),
  @Username        nvarchar(200),
  @DontLoadGuestCustomers  bit = 0,
  @PageSize        int = 2147483644,
  @PageIndex        int = 0,
  @TotalRecords      int = null OUTPUT
)
AS
BEGIN

  SET @Email = isnull(@Email, '')
  SET @Email = '%' + rtrim(ltrim(@Email)) + '%'
  
  SET @Username = isnull(@Username, '')
  SET @Username = '%' + rtrim(ltrim(@Username)) + '%'

  --paging
  DECLARE @PageLowerBound int
  DECLARE @PageUpperBound int
  DECLARE @RowsToReturn int
  DECLARE @TotalThreads int
  
  SET @RowsToReturn = @PageSize * (@PageIndex + 1)  
  SET @PageLowerBound = @PageSize * @PageIndex
  SET @PageUpperBound = @PageLowerBound + @PageSize + 1

  CREATE TABLE #PageIndex
  (
    IndexID int IDENTITY (1, 1) NOT NULL,
    CustomerID int NOT NULL,
    RegistrationDate datetime NOT NULL,
    Name nvarchar(200),
  )
  
  INSERT INTO #PageIndex (CustomerID, RegistrationDate, Name)
  SELECT DISTINCT  c.CustomerID, c.RegistrationDate, c3.Name
  FROM [Nop_Customer] c with (NOLOCK)
  LEFT OUTER JOIN (SELECT c1.CustomerId as CustomerId, (c1.Value + ' ' + c2.Value) as Name
  FROM (SELECT customerid, value FROM Nop_CustomerAttribute with (NOLOCK) where "Key" = 'FirstName') as c1
  LEFT OUTER JOIN (SELECT customerid, value FROM Nop_CustomerAttribute with (NOLOCK) where "Key" = 'LastName') as c2
  ON c1.CustomerID = c2.CustomerID) as c3
  ON c.CustomerID = c3.CustomerId
  WHERE
    (@StartTime is NULL or @StartTime <= c.RegistrationDate) and
    (@EndTime is NULL or @EndTime >= c.RegistrationDate) and
    (patindex(@Email, isnull(c.Email, '')) > 0) and
    (patindex(@Username, isnull(c.Username, '')) > 0) and
    (@DontLoadGuestCustomers = 0 or (c.IsGuest=0)) and
    c.deleted=0 OR
    (@StartTime is NULL or @StartTime <= c.RegistrationDate) and
    (@EndTime is NULL or @EndTime >= c.RegistrationDate) and
    (@DontLoadGuestCustomers = 0 or (c.IsGuest=0)) and
    (patindex(@Username, isnull(c.Username, '')) > 0) and    
    c3.Name LIKE '%'+@Email+'%'
  order by c.RegistrationDate desc

  
  SET @TotalRecords = @@rowcount  
  SET ROWCOUNT @RowsToReturn
  
  SELECT  
    c.CustomerId,
    c.CustomerGuid,
    c.Email,
    c.Username,
    c.PasswordHash,
    c.SaltKey,
    c.AffiliateId,
    c.BillingAddressId,
    c.ShippingAddressId,
    c.LastPaymentMethodId,
    c.LastAppliedCouponCode,
    c.GiftCardCouponCodes,
    c.CheckoutAttributes,
    c.LanguageId,
    c.CurrencyId,
    c.TaxDisplayTypeId,
    c.IsTaxExempt,
    c.IsAdmin,
    c.IsGuest,
    c.IsForumModerator,
    c.TotalForumPosts,
    c.Signature,
    c.AdminComment,
    c.Active,
    c.Deleted,
    c.RegistrationDate,
    c.TimeZoneId,
    c.AvatarId
  FROM
    #PageIndex [pi]
    INNER JOIN [Nop_Customer] c on c.CustomerID = [pi].CustomerID
  WHERE
    [pi].IndexID > @PageLowerBound AND
    [pi].IndexID < @PageUpperBound
  ORDER BY
    IndexID
  
  SET ROWCOUNT 0

  DROP TABLE #PageIndex
  
END


That is all... you should now be able to put in a customer's name and have it return results based off of their name. I used the "LIKE" operator so you don't have to have the customer's whole name or have it spelled correctly.

If you search for "Ron" it will return "Ronald", "Ronnie", etc.

Hope this helps!

Just changed the code... I mixed the code for adding another textbox with code for just using the email textbox. This code works fine with just the SQL modifcation... no modifcation is needed for any files.

Just what the doctor ordered, many thanks :)

BR
Joakim

Was having problems with the SQL code... recieving mixed results... just wasn't working properly. I fixed it and updated the code above. It seems to work a whole lot better now.

The only problem now is that if you use usernames you can't search for both username and name/email unless they are both correct for the same customer.

Hi bfranklin825!
I'm using nopCommerce 1.8 and when i run yout sql script, i have a problem:

An error occurred while executing the command definition. See the inner exception for details. 

Can you help me fix this problem! Thank's so much!

metallicamu wrote:

First of all, this is a dumb question, but did you change this:

USE [*YOURDATABASENAME*]

to reflect YOUR database name?

And... are there any other errors that you can post? It says to see the inner exception... can you post that?

Thank's bfranklin825! but certainly i changed Database is my Database!
I run sql command successful, but when i search Customer from Admin panal, that's error display in error message!
and where can i see the inner exception to know detail of the error?

metallicamu wrote:
Just checking!

Go to the "System" in the admin panel, there is a "log" there... there might be something useful there.

System.Data.SqlClient.SqlException: Procedure or function Nop_CustomerLoadAll has too many arguments specified

I had checked in store procedure but it is same with your procedure!
I don't know why it throws the exception!

Can you post what you have for the procedure so I can check it out?

I had the same issue looks like version 1.80 has added some new parameters I modified the stored procedure to the following and it worked

USE [*YOURDATABASENAME*]
GO
/****** Object: StoredProcedure [dbo].[Nop_CustomerLoadAll] Script Date: 09/09/2010 08:59:32 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[Nop_CustomerLoadAll]
(
  @StartTime        datetime = NULL,
  @EndTime        datetime = NULL,
  @Email          nvarchar(200),
  @Username        nvarchar(200),
  @DontLoadGuestCustomers  bit = 0,
  @DateOfBirthMonth    int = 0,
  @DateOfBirthDay      int = 0,
  @PageSize        int = 2147483644,
  @PageIndex        int = 0,
  @TotalRecords      int = null OUTPUT
)
AS
BEGIN

SET @Email = isnull(@Email, '')
SET @Email = '%' + rtrim(ltrim(@Email)) + '%'
SET @Username = isnull(@Username, '')
SET @Username = '%' + rtrim(ltrim(@Username)) + '%'

--paging
DECLARE @PageLowerBound int
DECLARE @PageUpperBound int
DECLARE @RowsToReturn int
DECLARE @TotalThreads int
SET @RowsToReturn = @PageSize * (@PageIndex + 1)
SET @PageLowerBound = @PageSize * @PageIndex
SET @PageUpperBound = @PageLowerBound + @PageSize + 1

CREATE TABLE #PageIndex
(
IndexID int IDENTITY (1, 1) NOT NULL,
CustomerID int NOT NULL,
RegistrationDate datetime NOT NULL,
Name nvarchar(200),
)
INSERT INTO #PageIndex (CustomerID, RegistrationDate, Name)
SELECT DISTINCT c.CustomerID, c.RegistrationDate, c3.Name
FROM [Nop_Customer] c with (NOLOCK)
LEFT OUTER JOIN (SELECT c1.CustomerId as CustomerId, (c1.Value + ' ' + c2.Value) as Name
FROM (SELECT customerid, value FROM Nop_CustomerAttribute with (NOLOCK) where "Key" = 'FirstName') as c1
LEFT OUTER JOIN (SELECT customerid, value FROM Nop_CustomerAttribute with (NOLOCK) where "Key" = 'LastName') as c2
ON c1.CustomerID = c2.CustomerID) as c3
ON c.CustomerID = c3.CustomerId
WHERE
(@StartTime is NULL or @StartTime <= c.RegistrationDate) and
(@EndTime is NULL or @EndTime >= c.RegistrationDate) and
(patindex(@Email, isnull(c.Email, '')) > 0) and
(patindex(@Username, isnull(c.Username, '')) > 0) and
(@DontLoadGuestCustomers = 0 or (c.IsGuest=0)) and
c.deleted=0 OR
(@StartTime is NULL or @StartTime <= c.RegistrationDate) and
(@EndTime is NULL or @EndTime >= c.RegistrationDate) and
(@DontLoadGuestCustomers = 0 or (c.IsGuest=0)) and
(patindex(@Username, isnull(c.Username, '')) > 0) and
c3.Name LIKE '%'+@Email+'%' and
    (@DateOfBirthMonth = 0 or (c.DateOfBirth is not null and DATEPART(month, c.DateOfBirth) = @DateOfBirthMonth)) and
    (@DateOfBirthDay = 0 or (c.DateOfBirth is not null and DATEPART(day, c.DateOfBirth) = @DateOfBirthDay))
order by c.RegistrationDate desc

SET @TotalRecords = @@rowcount
SET ROWCOUNT @RowsToReturn
  SELECT  
    c.CustomerId,
    c.CustomerGuid,
    c.Email,
    c.Username,
    c.PasswordHash,
    c.SaltKey,
    c.AffiliateId,
    c.BillingAddressId,
    c.ShippingAddressId,
    c.LastPaymentMethodId,
    c.LastAppliedCouponCode,
    c.GiftCardCouponCodes,
    c.CheckoutAttributes,
    c.LanguageId,
    c.CurrencyId,
    c.TaxDisplayTypeId,
    c.IsTaxExempt,
    c.IsAdmin,
    c.IsGuest,
    c.IsForumModerator,
    c.TotalForumPosts,
    c.Signature,
    c.AdminComment,
    c.Active,
    c.Deleted,
    c.RegistrationDate,
    c.TimeZoneId,
    c.AvatarId,
    c.DateOfBirth
  FROM
    #PageIndex [pi]
  INNER JOIN [Nop_Customer] c on c.CustomerID = [pi].CustomerID
  WHERE
    [pi].IndexID > @PageLowerBound AND
    [pi].IndexID < @PageUpperBound
  ORDER BY
    IndexID

SET ROWCOUNT 0

DROP TABLE #PageIndex
END