What's the best way to interrogate the OpenVPMS MySQL database

Hello,

  I need to pull some details out of our customer database - what's the best way of doing it? Details like customer emails, number of customers, addresses etc. A SQL dump?  Is there a map of how the tables are laid out somewhere (I'm a bit of a MySQL novice!)

 

Thanks in advance.

Comment viewing options

Select your preferred way to display the comments and click "Save settings" to activate your changes.

Re: What's the best way to interrogate the OpenVPMS MySQL ...

Take a look at the export reports included in 1.8.1:

  • Export Customer Addresses
  • Export Customer Addresses for Sales
  • Export Customer Email Addresses
  • Export Customer Email Addresses for Sales

These are JasperReports that can be used to export customer data to CSV. They are launched from from Reporting -> Reports.

If you haven't loaded them, they are included in the <OPENVPMS_HOME>/reports/Reporting/Reports/Customer/A4 directory.

 

If they don't do precisely what you want, they can be customised in Jaspersoft Studio

 

Re: What's the best way to interrogate the OpenVPMS MySQL ...

Cahir - you find that these reports go to some trouble to extract only the best address.  If you want something that shows all the addresses or emails that a customer has, then you are going to have to tweak the report - but it is not difficult - just move all the fields in the group header to the detail band and then delete the group.

Regards, Tim G

Re: What's the best way to interrogate the OpenVPMS MySQL ...

Thanks guys - these were perfect.  I don't suppose anyone has a prepped report for telephone numbers before I go tweaking in Jasper Studio?

Re: What's the best way to interrogate the OpenVPMS MySQL ...

Or better yet, a report that combines name, address, telephone number(s) and emails & patients!

Re: What's the best way to interrogate the OpenVPMS MySQL ...

Cahir - are you after a 'print all info for one customer' or 'print all info for lots of customers'.  If the second, what are the selection criteria?

Note that I suspect the way to build this report is to have the main report handle the customer selection and base information and then use a contacts subreport, and a patients subreport.

Questions to ponder:

1) what customer financial information should be displayed?

2) what patient information should be displayed? [eg reminders, med history, ...]

3) do you need a printed report, or an export report?

4) where there are multiple contacts of one type, should all be included?

NOTE: I am NOT committing to building such a report - partially because I am busy, and partially because I do not understand the purpose of the report. [Wanting phone contacts implies that you are going to call them, and this implies that you need a list of who to call, and this implies a requirement for some sort of selection. If so you might be better off having an appropriate customer selection report, and then call up each customer on the screen and read off the required details.]

AHAH - I understand - you are trying to build calling scripts for a call centre in Bangalore to which you have outsourced your Customer Relationships Management and you need to fill in "Hello [title] [name] I am calling about your pet[s] {[pname1] [,pname2] [,pname3]...}. Did you know that this week are are having a special on ....." 

Or don't the Irish suffer from the imposition of being called by Indian gentlemen wanting to sell you solar panels/etc etc etc.

Regards, Tim G

Re: What's the best way to interrogate the OpenVPMS MySQL ...

Actually - would be happy to pay if someone could create a report listing all active patients for all clients.  We occasionally export data like this for marketing mail shots.  Anyone got any idea re costing?

Re: What's the best way to interrogate the OpenVPMS MySQL ...

Cahir - see http://www.openvpms.org/customisation/export-customer-addresses-patient-... in the resource library. Note that it says version 1.7+. I have only tested it on my 1.9 system but to the best of my knowledge it should run under 1.7

It took me around 3 hours. Donate to one of the 1.9 projects - see http://www.openvpms.org/forum/steering-committee-voting-results that is not yet done (Comms logging & HTML email are up and running) - say Schedule Blocking.

Regards, Tim G

PS if the report has bugs, yell quick

 

Re: What's the best way to interrogate the OpenVPMS MySQL ...

That project is already being implemented for 1.9 using the development levy. There are plenty of other projects to choose from at http://www.openvpms.org/projects

Syndicate content