Company: Link Technologies
Case No: L12381
Logged By: Sanjay (Link Technologies) on 12 Jul 2011 06:17PM
Priority: Not Applicable
Product: Other
Group: To be assigned
Time Taken: 0.00
Assigned To: Sanjay (Link Technologies)
Circulation: Sanjay
Resolve By: Tuesday, 12 July 2011 06:17 PM [4644 days since logged date]
Status: Closed
Subject: NOP Commerce - Extract Customer Information
Summary:    The NOP Commerce Database holds customer contacts in three tables:

1. nop_customer
2. nop_customerAttribute - This holds primary contact information
3. nop_address - Billing and Shipping information

The script below extracts this information from the NOP Database into a tabular format.


declare @ReportData table
(
CustomerID int,
Email varchar(100),
UserName varchar(100),
RegistrationDate varchar(100),
FirstName varchar(100),
LastName varchar(100),
Company varchar(100),
StreetAddress varchar(100),
StreetAddress2 varchar(100),
ZipPostalCode varchar(100),
City varchar(100),
PhoneNumber varchar(100)
)

Insert into @ReportData (CustomerID, Email, UserName)
Select CustomerID, Email, UserName from nop_customer

Update @ReportData set Firstname = b.Value
from @ReportData a inner join nop_customerAttribute b on a.CustomerID = b.CustomerID and b.[key] = 'FirstName'

Update @ReportData set LastName = b.Value
from @ReportData a inner join nop_customerAttribute b on a.CustomerID = b.CustomerID and b.[key] = 'LastName'


Update @ReportData set Company = b.Value
from @ReportData a inner join nop_customerAttribute b on a.CustomerID = b.CustomerID and b.[key] = 'Company'

Update @ReportData set StreetAddress = b.Value
from @ReportData a inner join nop_customerAttribute b on a.CustomerID = b.CustomerID and b.[key] = 'StreetAddress'

Update @ReportData set StreetAddress2 = b.Value
from @ReportData a inner join nop_customerAttribute b on a.CustomerID = b.CustomerID and b.[key] = 'StreetAddress2'

Update @ReportData set ZipPostalCode = b.Value
from @ReportData a inner join nop_customerAttribute b on a.CustomerID = b.CustomerID and b.[key] = 'ZipPostalCode'

Update @ReportData set City = b.Value
from @ReportData a inner join nop_customerAttribute b on a.CustomerID = b.CustomerID and b.[key] = 'City'

Update @ReportData set PhoneNumber = b.Value
from @ReportData a inner join nop_customerAttribute b on a.CustomerID = b.CustomerID and b.[key] = 'PhoneNumber'

select * from @ReportData
Audit Notes:
If you have any queries regarding this support incident, please email admin@linktechnologies.com.au and include the Case No: L12381 in the subject line of all emails regarding this issue.

Document size: 2.3 KB
For call complaints, please contact the Managing Director of the company using this form