Customer Search (by name)

Hello,

I have been testing nopcommerce for some days now.
however there is one thing that makes me completely unable to use it.

In the admin backend, i can search on email, etc etc.
However is there a easy way to turn this into name (first / last name)
or address?

As with our current system we use this function quite alot (we provide service on location).

The reason why i require this function is duo to not all our customers having a Email address.

Can anyone help me out?

Regards,
Marco

I have figured out a way to change the "Email" box in manage customers to a "Name or Email" box. It requires some editing and 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]
ALTER PROCEDURE [dbo].[Nop_CustomerLoadAll]
(
  @StartTime        datetime = NULL,
  @EndTime        datetime = NULL,
  @Name          nvarchar(200),
  @Email          nvarchar(200),
  @Username        nvarchar(200),
  @DontLoadGuestCustomers  bit = 0,
  @PageSize        int = 2147483644,
  @PageIndex        int = 0,
  @TotalRecords      int = null OUTPUT
)
AS
BEGIN

  SET @Name = isnull(@Email, '')
  SET @Name = '%' + rtrim(ltrim(@Email)) + '%'
  
  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 #CustomerName
  (
    IndexID int IDENTITY (1, 1) NOT NULL,
    CustomerID int NOT NULL,
    CustomerName nvarchar(200) NOT NULL,
    RegDate datetime NOT NULL,
  )
  
  INSERT INTO #CustomerName(CustomerID, CustomerName, RegDate)
  SELECT f.CustomerId as 'CusId', (f.Value + ' ' +l.Value) as 'Name', c.RegistrationDate as 'RegDate'
  FROM [Nop_CustomerAttribute] as f
  LEFT OUTER JOIN [Nop_CustomerAttribute] as l
  ON f.CustomerId = l.CustomerId
  LEFT OUTER JOIN [Nop_Customer] as c
  ON f.CustomerId = c.CustomerID
  WHERE f.[Key] = 'FirstName' AND l.[Key] = 'LastName'
  ORDER BY c.RegistrationDate desc
    
  CREATE TABLE #PageIndex
  (
    IndexID int IDENTITY (1, 1) NOT NULL,
    CustomerID int NOT NULL,
    RegistrationDate datetime NOT NULL,
  )
  
  INSERT INTO #PageIndex (CustomerID, RegistrationDate)
  SELECT DISTINCT
    c.CustomerID, c.RegistrationDate
  FROM [Nop_Customer] c with (NOLOCK)
  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
  order by c.RegistrationDate desc

  IF @@rowcount = 0
  INSERT INTO #PageIndex (CustomerID, RegistrationDate)
  SELECT CustomerID, RegDate
  FROM #CustomerName
  WHERE CustomerName LIKE @Name
  
  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 #CustomerName
  DROP TABLE #PageIndex
  
END

Step 3.

In "Customers.ascx.cs" (administration/modules) at line 74 is this:

string email = txtEmail.Text.Trim();

Add this right below it on the next line:

string name = txtEmail.Text.Trim();

At line 80 add "name" between "endDate" and "email"

Step 4.

In "CustomerManager.cs" (Nop.BusinessLogic/Customer)...

Line 842 add "string.Empty" after the third "null" (there should end up being two "string.Emptys")

Right after line 850 add: "/// <param name="Name">Customer Name</param>"

Line 860 add "string Name" after "DateTime? registrationTo"

Line 881 add "Name" after "registrationTo"

Step 5.

In "NopObjectContext.cs" (Nop.BusinessLogic/Data)...

Line 1727 add "string name" right after "DateTime? registrationTo"

Right after line 1750 add "ObjectParameter nameParameter = new ObjectParameter("name", name);"

Line 1761 add "nameParameter" after "endTimeParameter"

Step 6.

In NopModel.edmx (Nop.BusinessLogic/Data)...

Right after line 3835 add "<Parameter Name="Name" Type="nvarchar" Mode="In"/>"

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!

hello, thanx for your reply, i tried your solution but i think something gone wrong with the SQL script.

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

and in de debug window:
A first chance exception of type 'System.Data.EntityCommandExecutionException' occurred in System.Data.Entity.dll
AspNetObjectContextManager: Disposed ObjectContext
The thread '<No Name>' (0x15bc) has exited with code 0 (0x0).
The thread '<No Name>' (0xa14) has exited with code 0 (0x0).
The thread '<No Name>' (0xd9c) has exited with code 0 (0x0).
The thread '<No Name>' (0x1424) has exited with code 0 (0x0).
The thread '<No Name>' (0x13e8) has exited with code 0 (0x0).
The thread '<No Name>' (0x15d0) has exited with code 0 (0x0).
The thread '<No Name>' (0x6f8) has exited with code 0 (0x0).
The thread '<No Name>' (0x1470) has exited with code 0 (0x0).


This even shows up on the non edited site (i had a backup which i checked)
So i am guessing this is a Database related problem?

Regards,
Marco

First off... what version are you using? I tested this code with nopCommerce 1.70. And what are you using to edit the SQL?

Also, can you post your Nop_CustomerLoadAll stored procedure here?

i'm using the latest 1.80

to push the que i used Microsoft visual studio 2010 to execute SQL commands
it did make some changes that i am sure of but i think it's looking up something that isn't there <No Name>

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

  SET @Name = isnull(@Name, '')
  SET @Name = '%' + rtrim(ltrim(@Name)) + '%'
  
  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 Nop_CustomerName
  (
    IndexID int IDENTITY (1, 1) NOT NULL,
    CustomerID int NOT NULL,
    CustomerName nvarchar(200) NOT NULL,
    RegDate datetime NOT NULL,
  )
  
  INSERT INTO Nop_CustomerName(CustomerID, CustomerName, RegDate)
  SELECT f.CustomerId as 'CusId', (f.Value + ' ' +l.Value) as 'Name', c.RegistrationDate as 'RegDate'
  FROM [Nop_CustomerAttribute] as f
  LEFT OUTER JOIN [Nop_CustomerAttribute] as l
  ON f.CustomerId = l.CustomerId
  LEFT OUTER JOIN [Nop_Customer] as c
  ON f.CustomerId = c.CustomerID
  WHERE f.[Key] = 'FirstName' AND l.[Key] = 'LastName'
  ORDER BY c.RegistrationDate desc
    
  CREATE TABLE Nop_PageIndex
  (
    IndexID int IDENTITY (1, 1) NOT NULL,
    CustomerID int NOT NULL,
    RegistrationDate datetime NOT NULL,
  )
  
  INSERT INTO Nop_PageIndex (CustomerID, RegistrationDate)
  SELECT DISTINCT
    c.CustomerID, c.RegistrationDate
  FROM [Nop_Customer] c with (NOLOCK)
  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
  order by c.RegistrationDate desc

  IF @@rowcount = 0
  INSERT INTO Nop_PageIndex (CustomerID, RegistrationDate)
  SELECT CustomerID, RegDate
  FROM Nop_CustomerName
  WHERE CustomerName LIKE @Name
  
  SET @TotalRecords = @@rowcount  
  SET ROWCOUNT @RowsToReturn
  
  SELECT  
    c.CustomerId,
    c.CustomerGuid,
    c.Email,
  c.Name,
    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
    Nop_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 Nop_CustomerName
  DROP TABLE Nop_PageIndex
  
END

Thats in my Stored Procedures --> Nop_CustomerLoadAll
however i keep wondering what Nop_CustomerName is and Nop_PageIndex
as their not in the database tables

Oh

c.Name and
SET @Name = isnull(@Name, '')
  SET @Name = '%' + rtrim(ltrim(@Name)) + '%'

used to be:
SET @Name = isnull(@Email, '')
  SET @Name = '%' + rtrim(ltrim(@Email)) + '%'
as you wrote.. i was just trying to fix it meanwhile (discovering why it gives the error)

Did you change #CustomerName and #PageIndex to Nop_CustomerName Nop_PageIndex?

That is the only difference that I see from my code. These two tables are temporary tables created to put together data in one table that comes from several tables. They are created and then destroyed in the stored procedure- they don't actually exist in the database.

You can take c.Name out... there is no name in the customer table, that is stored in the customer attribute table...

Let me get back to you later....

I made a mistake in this code... I mixed it up with code to add a new textbox... you don't actually have to change any files to use the email textbox for the name, just the SQL stored procedure... the code is here:

https://www.nopcommerce.com/boards/t/6095/search-for-customer-by-name-in-administration.aspx

I'm sorry for the confusion.