I have written a couple of Queries for getting user data. I have implemented then on a site in a stats report so the admin can copy the data into excel. I know this has been done before (see:
http://forum.umbraco.org/extending-umbraco/export-members-to-xml---looking-for-a-faster-way) but this is a bit more flexible.
The first query is a basic one that just gets basic user info:
The second query will get all user data + all extended member property data. It will also look up member parameter descriptions of choices such as radiocontrols instead of giving the ambiguous ID's (note to do this, radiocontrols etc must store the data as Int's.
I would be great full if someone could make a basic .Net controll(s) that will accept some SQL/plus parameters and: populate a grid, allow download to excel, create xml.
Code:
SELECT MEMBTYPEID.text AS MemberType,
MEMBLST.nodeId ,
MEMBNODE.TEXT ,
MEMB.LoginName ,
MEMB.Email ,
CONVERT(SMALLDATETIME, MEMBNODE.createDate) AS SignUpdate
FROM
(SELECT id,
text
FROM dbo.umbracoNode
WHERE (nodeObjectType = '9b5416fb-e72f-45a9-a07b-5a9a2709ce43')
) AS MEMBTYPEID
LEFT OUTER JOIN
(SELECT nodeId,
contentType
FROM dbo.cmsContent
) AS MEMBLST
ON MEMBLST.contentType = MEMBTYPEID.id
LEFT OUTER JOIN dbo.cmsMember AS MEMB
ON MEMB.nodeId = MEMBLST.nodeId
LEFT OUTER JOIN dbo.umbracoNode AS MEMBNODE
ON MEMBNODE.id = MEMBLST.nodeId
The second query gets
Code:
SELECT MEMBTYPEID.text AS MemberType, MEMBLST.nodeId, MEMBTYPES.Name AS MemberField,
ISNULL(CASE WHEN MEMBTYPES.datatypeID IN
(SELECT NodeId
FROM DBO.CMSDATATYPE
WHERE DBTYPE = 'Nvarchar') THEN MEMBDATA.[dataNvarchar] WHEN MEMBTYPES.datatypeID IN
(SELECT NodeId
FROM DBO.CMSDATATYPE
WHERE DBTYPE = 'Ntext') THEN MEMBDATA.[dataNtext] WHEN MEMBTYPES.datatypeID IN
(SELECT NodeId
FROM DBO.CMSDATATYPE
WHERE DBTYPE = 'Date') THEN CONVERT(NVARCHAR, MEMBDATA.[dataDate]) WHEN MEMBTYPES.datatypeID IN
(SELECT NodeId
FROM DBO.CMSDATATYPE
WHERE DBTYPE = 'Integer') THEN CASE WHEN
(SELECT value
FROM [dbo].[cmsDataTypePreValues]
WHERE datatypenodeid = MEMBTYPES.[dataTypeId] AND id = CONVERT(INT, MEMBDATA.[dataInt])) IS NOT NULL THEN
(SELECT value
FROM [dbo].[cmsDataTypePreValues]
WHERE datatypenodeid = MEMBTYPES.[dataTypeId] AND id = CONVERT(INT, MEMBDATA.[dataInt])) ELSE CONVERT(NVARCHAR,
MEMBDATA.[dataInt]) END ELSE NULL END, '') AS MemberData
FROM (SELECT id, text
FROM dbo.umbracoNode
WHERE (nodeObjectType = '9b5416fb-e72f-45a9-a07b-5a9a2709ce43')) AS MEMBTYPEID LEFT OUTER JOIN
(SELECT nodeId, contentType
FROM dbo.cmsContent) AS MEMBLST ON MEMBLST.contentType = MEMBTYPEID.id LEFT OUTER JOIN
dbo.cmsPropertyType AS MEMBTYPES ON MEMBTYPES.contentTypeId = MEMBLST.contentType LEFT OUTER JOIN
dbo.cmsPropertyData AS MEMBDATA ON MEMBDATA.contentNodeId = MEMBLST.nodeId AND
MEMBDATA.propertytypeid = MEMBTYPES.id LEFT OUTER JOIN
dbo.cmsMember AS MEMB ON MEMB.nodeId = MEMBLST.nodeId
WHERE (MEMBLST.nodeId IS NOT NULL)