Another day of dotnetnuke today. This time I’ve been working on the CataLook store module with paypal integration. After a previous integration with barclays epdq I have to say paypal is (so far) a breeze and a pleasure.
I always get stuck with something though – and today was no exception, getting a quick report report for the accounts department showing all orders complete with the dotnetnuke profile info…
Anyway in the end I found a code snippet from the accidental geek which saved my bacon.
An update on the post shows a rather quick way of pulling profile information out of the database,couple this with a quick tweak, and displaying the results through the fantastic Tressleworks SQLGridSelectView and I have my report.
The sql query is shown here
SELECT o.OrderID, o.CustomerID, od.InvoiceNumber,o.OrderDate, u.FirstName, u.LastName, u.Email, od.ProductID, p.ProductName, od.Quantity, od.UnitCost, CAST(od.tax as decimal(7,2)) as VATRate, (od.Quantity * od.UnitCost) AS Net, CAST((od.Quantity * od.UnitCost * (od.Tax/100)) AS decimal(7,2)) AS VAT,CAST ((od.Quantity * od.UnitCost) + (od.Quantity * od.UnitCost * (od.Tax/100)) AS decimal(7,2)) as Gross, o.paymenttype, o.paymentstate,
upd.Company,
upd.Street,
upd.City,
upd.PostalCode,
upd.Telephone
FROM CAT_OrderDetails od
INNER JOIN CAT_Orders o ON od.orderid = o.orderid
INNER JOIN CAT_Products p ON od.ProductID = p.ProductID
INNER JOIN Users u on o.CustomerID = u.UserID
LEFT OUTER JOIN
(SELECT
up.UserID,
MAX(CASE WHEN ppd.PropertyName = 'Company' THEN up.PropertyValue ELSE '' END) AS Company,
MAX(CASE WHEN ppd.PropertyName = 'Street' THEN up.PropertyValue ELSE '' END) AS Street,
MAX(CASE WHEN ppd.PropertyName = 'City' THEN up.PropertyValue ELSE '' END) AS City,
MAX(CASE WHEN ppd.PropertyName = 'PostalCode' THEN up.PropertyValue ELSE '' END) AS PostalCode,
MAX(CASE WHEN ppd.PropertyName = 'Telephone' THEN up.PropertyValue ELSE '' END) AS Telephone
FROM
dbo.UserProfile AS up INNER JOIN
dbo.ProfilePropertyDefinition AS ppd ON up.PropertyDefinitionID = ppd.PropertyDefinitionID and ppd.PortalID = 0
Group By up.UserID) as upd on u.UserID = upd.UserID
order by OrderDate DESC
Tags: catalook, dotnetnuke, sql
