The way that I intended usernames to be stored is in a new table that holds the attributes for that service. They are called _options tables. For example, an email service would have a table called email_options that might hold a username, password, a record of virtual mail mappings, etc.
The table is indicated by the Options Table field in the service editor. Simply name the table something_options and then go to the Options Tables link in the service editor and click Create. This will make a blank table with the two required fields, then you can add any additional attribute fields to it using any mysql tool, like phpMyAdmin. If you look at the default install of CitrusDB there is an example service in there with an example_options table that holds the username and a search query that is stored in the searches table (id 9), then there is HTML in the modules/services/search.php file that adds the search field to the search page for that query. You can add new SQL queries to the searches table, the only requirement is that the result has the account_number field returned, so that it knows what account to link the results to. You may need to JOIN tables together to get this. Then edit the search.php file to add your new search field and make it show up in the web browser. There is also a field in the service editor called Activation String, in the case of the example service it says "username,password". This indicates what fields from the options table are to be included with the output of the Activate Accounts function. When the activate accounts function is working, which will be in the upcoming 1.2 release it will be able to create a file with a list of accounts that is meant to be processed by an account management script for adding, deleting, enabling, or disabling accounts. This idea of options tables is also the reason I don't think I can make citrusdb integrate that directly with radius to rely on for storing usernames and passwords, since there are many services that don't have usernames and passwords like leased lines or some DSL circuits, never mind non telecom services like consulting and subscription services. CitrusDB doesn't actually recognize a username or password as something unique, it's simply another field in an options table, among any fields. Paul On Nov 30, 2007 11:24 AM, kreg <[EMAIL PROTECTED]> wrote: > > Hello, > > Would you be able to provide us some more custom queries? Here's the > story. > > We are trying to build the interface to search for usernames. Our > usernames are in the form of email addresses. Therefore we just use the > Contact Email field in the customer summary screen as the place to store the > accounts "username". Our customers usernames are what they use to login to > the internet with dialup or pppoe broadband. The usersnames are stored on a > completely different system and database. We just keep CitrusDB upto date > as best as we can manually. (unless you know some magic to sync another > database) > > Ideas? > > -Kreg > > > > Paul Yasi wrote: > Any of these kinds of queries are possible, though you need to know > about SQL JOIN's to get them done. Here are some examples: > > To query a city with a specific service, you need to know the specific > service ID: > > SELECT * > FROM customer > LEFT JOIN user_services ON user_services.account_number = > customer.account_number > WHERE customer.city LIKE '%Kansas City%' > AND user_services.master_service_id =3 > > If you wanted to query everyone with a service category you would join > the master_service table to the query: > > SELECT * > FROM customer > LEFT JOIN user_services ON user_services.account_number = > customer.account_number > LEFT JOIN master_services ON master_services.id = > user_services.master_service_id > WHERE customer.city LIKE '%Kansas City%' > AND master_services.category = "dsl" > > Paul > > On 5/14/07, kreg <[EMAIL PROTECTED]> wrote: > > > All of our users in the database have a primary contact email address. > > I am trying to get a list of all contact email addresses matching their City > and certain added Services. > > For example: > I would like a list of email addresses so that I can send an email to all > customers who live in > Kansas City and have DSL service for an announcement. > > -Kreg > > ------------------------------------------------------------------------- > This SF.net email is sponsored by DB2 Express > Download DB2 Express C - the FREE version of DB2 express and take > control of your XML. No limits. Just data. Click to get it now. > http://sourceforge.net/powerbar/db2/ > _______________________________________________ > Citrusdb-users mailing list > Citrusdb-users@lists.sourceforge.net > https://lists.sourceforge.net/lists/listinfo/citrusdb-users > > > > > > > -- > > > > Kreg Roenfeldt > Systems Administrator > ---------------------------------------- > p 208.548.2345 > f 208.548.9911 > e [EMAIL PROTECTED] > ---------------------------------------- > 150 South Main > P.O. Box 270 > Rockland, ID 83271-0270 > > > ------------------------------------------------------------------------- > SF.Net email is sponsored by: The Future of Linux Business White Paper > from Novell. From the desktop to the data center, Linux is going > mainstream. Let it simplify your IT future. > http://altfarm.mediaplex.com/ad/ck/8857-50307-18918-4 > _______________________________________________ > Citrusdb-users mailing list > Citrusdb-users@lists.sourceforge.net > https://lists.sourceforge.net/lists/listinfo/citrusdb-users > > -- The CitrusDB Project | http://www.citrusdb.org Open Source Customer Care & Billing System ------------------------------------------------------------------------- SF.Net email is sponsored by: The Future of Linux Business White Paper from Novell. From the desktop to the data center, Linux is going mainstream. Let it simplify your IT future. http://altfarm.mediaplex.com/ad/ck/8857-50307-18918-4 _______________________________________________ Citrusdb-users mailing list Citrusdb-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/citrusdb-users
