Dear All,

Many of the grid/reports modules produced for DNN make use a flat table structure. Most users of these modules like to view users in the DNN-build-in user tables but find the stucture with default userdata in one table and additional profile fields sored in seperate fields/tables to difficult. It would be great if someone could create a view or stored procedure that produces one or more flat table(s) that is/are genereated based on the Useraccount data stored in the DNN Database. The following fields would need to be included (default DNN fields):

• Username
• FullName (Concatenation of FirstName, MiddleName and LastName fields stored)
• DisplayName
• Firstname
• MiddleName
• Lastname
• Address (Concatenation of Street and  Unit field stored)
• Street
• Unit
• City
• Region
• Country
• PostalCode
• Telephone
• Cell
• Fax
• Website
• E-Mail
• CreatedDate
• PortalID (*)
• PortalName (*)
• Blocked (0 or 1) (*)
• Authorized (0 or 1) (*)
• DisplayUser (Blocked + Authorized, i.e. 0 = Do not Display user in grids, 1 = Display user in grids) (*)
• LastLoginDate (*)
• LastActivityDate (*)
• PasswordDate (*)
• BlockingDate (*)
• OnLine (0 or 1) (*)
• ChangePassword (0 or 1) (*)

I think the best would be if (at least) four seperate views were to be produced:

1. DNNUsers             Private View for Administrors/Hosts only (all of the above fields)
2. ActiveDNNUsers  Common View (all of the above fields, only records that have DisplayUser = 1)
3. PortalUsers           Common View (all of the above fields, only records that have DisplayUser = 1, for one particular portal)
4. Contacts                 Public View (all of the fields above with the exception of the fields marked with "(*)", only records with DisplayUser = 1, for one particular portal)

Most Hosts have multiple Portals on one DNN instance (it's to labour intensive to have one DNN instance per department/customer). Clearly it's not wanted a simple contacts report produced by a vistor of Portal A would include records from the users in Portal B.  Maybe it's the best to generate one view per PortalID (e.g. MyWebsite_contacts or Portal_0_contacts).  Other suggestions on how to ensure that users in one portal can not access/see users of another portal via SQL are much appreciated (maybe DNN 5.0+??).

Ofcourse the producer of this (OpenSource) sollution may place comments incl. author name/company/website in the SQL statements that generate the requested result.

Some people don't speak english (I know; it's weird). A nice-to-have feature  would be if the fieldnames could be localized (e.g. Naam in stead of Name).

Visitors of this Forum can place their suggestions/sollutions in this forum and/or send their work in progress to Info@ShareIT.nu.

As a token of appreciation, on behalf of the DNN Community,  I promise to send 6 bottles of fine French champagne to the person/company/usergroup that produces the best (and working) result by November1st.

BTW: Please don't  reply with answers such as "Why not use the users modules of DNN Masters or similar?". I'm well aware of the existance of such modules but  seek an OpenSource solution for the tens of thousands of users of the standard DotNetNuke Reports/SQL modules and users of other OpenSource/Commercial products such as ReportGrid, SQL Selected Grid, SQL Advanced Data Grid,  SQLView, SQLGridView etc.

Thanks for yor reply,

Jeroen van Craaikamp
The Netherlands

www.ChampagneUnLimited.nl
www.ShareIT.nu
www.PleskServices.nl

I partly agree with the points mentioned.

1. Indeed it is not the most elegant way to display the content of the DNN database via SQL views. However, may people do. There is an ever increasing demand for modules that access the database such as ReportGrid, SQL Grids etc. An official DNN project even exist to produce a Report module with SQL support.
2. Suppose you run a DNN instance with only one portal. Then, only user data from that portal can be displayed. What's the actual problem then?
3. IMHO Business objects have the same practical problems as the requested view. If the data layer is changed  or if  profile fields (such as phone number) are deleted/renamed most of these modules fail to work as well. Then I'd rather have a view that I can maintain/modify myself then having a PA of a Business Module that may not support the change in data layer or profile properties. (remember the disasters when people upgraded to DNN 4.X?)
4. You are right that users can enable/disable public view rights on particular profile fields. So rather than scrap the idea, let's adress this issue and populate only the fields with profile data if the user has authorised public viewing of this field. For the Admin/Host views I would still like to see the whole lot (same as they can see via Admin/Host menu).

The problems that I have with the available User Modules is:

• Admins on a shared DNN instance are not allowed to install modules themselves.
• I don't want portal admins to supply all kinds of Contact modules since then i have to maintain and upgrade these modules as well when i upgrade DNN instance. It's already very labour intensive to re-test the 20 or so Premium modules (that I have) with each new DNN release.
• The modules available either only provide the basic list (username, name, email) or are to expensive for private persons with a family home page.
• Some modules don't even offer a contact detail view but only a grid view. Master/detail grids are only supported by very few contact modules.
• The proffesional modules (such as the suites from DNN masters) are too complex for simple users and/or offer too much functionality (multi portal viewing).for regular users.
• Other contact modules have their data strored in seperate tables. However, then visitors have to enter their data in the user account as well as in the contacts module. It's just a matter of time before contacts module data and user data will be out of synch.  Also, the Admin then needs to do things double when a user is to be blocked/removed.

Maybe the solution to the probem would be if someone would make a contacts module:

1. That makes use of the user data from the DNN Database (default data as well as profile data)
2. That handles/reports change/deletion of DNN profile properties within a portal
3. That takes into account that users may have disabled viewing rights on some field content.
4. That provides a tailorable list/grid view (subset of available fields incl. profile fields)
   • Customizable grid layout via HTML (both layout as well as available field selection)
   • That allows searching on 1 or more of the shown fields (and/or)
   • That allows filtering on 1 or more of the shown fields (and./or)
   • Supports CSS
   • That supports paging
   • That can initatiate display of the relevant details view on the same page or an other page
5. That provides a tailorable detail view (subset of available fields, incl. profile fields)
   • Than can be called
     • via a selection in the grid view 
     • via a username parameter that is send via the url of the page that contains the details
   • Customizable detail layout via HTML (both table/grid layout as well as field selection)
   • Supports CSS
   • That allows searching on 1 or more of the shown fields (and/or)
   • That allows filtering on 1 or more of the shown fields (and/or)
   • That can be split over multiple tabs/pages
6. That is easy to use
   • by providing a set of pre-designed html templates for grid and detail views.
   • Standard, Pro and Admin version?
7. That has the typical DNN look and feel
8. That supports localisation
9. That supports HTML output (for images, url's etc)
10. That prevents access to user data in other portals for non-hosts.
11. That supports export of selected users with selected fields to Excel
12. That supports export of selected users with selected fields to XML
13. That supports export of selected users with selected fields to Outlook  VCF and/or PST files
14. That supports Email to selected users by logged-in users.
15. For which the admin can switch on/off options 10-15
16. For which the PA is protected against deployment on more than one portal
17. For a retail price of less than 45 Euro per PA (199 Euro for a Enterprise/Server License)
18. For which a new release/patch is brought out  to support new DNN releases (restrictions may apply).

If somebody is interested to build such a module, I can make a full functional specification.

Thanks,

Jeroen

BTW: The offer for the bottles for the view still stands...