We run a Clinical Psychology Practice that provides services to corporate and private clients. We need a Clinical Records and Risk Management system, to help us manage case records, streamline billing, and reduce risk. Having researched WordPress capabilies, and experimented with it, we believe the requirements can be met with some wordpress customisations and additional coding.
Please note the following:
* I have some HTML skills (I used to develop websites when you could still do it in notepad ie before HTML3 :-) ) and understand SQL and C++ - so I will want to be able to discuss different ways of doing things at a technical level
* I've included a number of questions to the bidders within the document, highighted inside a box. To ensure that bidders are reading the spec before bidding and are bidding based on reading the spec and not just the description, please can you comment on whether you think using WP Categories as the Case container makes sense, and whether it's possible to manage and edit billing line items for posts in the way I've suggested [Any bid that does not answer these two questions will be ignored]. I want to make sure that you understand the detail of what we need from the system, and know enough about WordPress internals, MySQL, web programming and PHP to come up with a good solution that can be used as a building block for the future phases of functionality.
* I need the solution to be well documented. I need a register of every WP file that is modified or copied, and a description of what has been changed - and the changes need to be clearly documented with comments (in English) in the code. This is so that future contractors can build on the work. I understand that good documentation takes time and time costs money, but I would rather have a well documented and future-proof solution. Please do not under-estimate the work and then feel pressured to do a rush job
* This is work for hire, and the copyright to any code written will pass to the CherryCroft practice once the contract is concluded (work completed and paid for)
* Please comment on whether you've integrated with Xero before and whether there are any issues with doing so
I've added a simple Entity-Relationship Diagram to demonstrate how the data objects relate (standard 0/1 to many connectors show the ordinality of relationships between entities).
User, Case and Record are not separate table entities in WordPress, based on my vision, a Case is a Category so it is stored in wp_term and wp_termmeta, and a Records is acustom post type so its data (including custom fields) is stored in wp_post and wp_postmeta. user is a standard WP user, augmented with the WP-CRM plugin which uses wp_usermeta to store additional fields for each user.
The "crrm_*" entities are new custom database rables, stored in the "wordpress" schema and accessed from PHP using $wpdb() object. My preference is to use standalone tables for the financial data (contract header/lineitem and billing) rather than storing financial data as meta fields inside wp_postmeta, to make things like financial audit and reconcolisation easier - I can then use SELECT statements in MySQL to view and manipulate the financial data, and use MySQL Excel connectors to pull reporting into MS Excel from MySQL.