Using SQL Views for custom Dashboard cases display?

[riddler]

I did some poking around. In the database, I found this View. civicrm_view_case_activity_upcoming and civicrm_view_case_activity_recent

Code: [Select]

CREATE ALGORITHM=UNDEFINED DEFINER=`{username}`@`{IP}/{IP}` SQL SECURITY DEFINER VIEW `civicrm_view_case_activity_upcoming` AS select `ca`.`case_id` AS `case_id`,`a`.`id` AS `id`,`a`.`activity_date_time` AS `activity_date_time`,`a`.`status_id` AS `status_id`,`a`.`activity_type_id` AS `activity_type_id` from (`civicrm_case_activity` `ca` join `civicrm_activity` `a` on((`ca`.`activity_id` = `a`.`id`))) where ((`a`.`activity_date_time` <= (now() + interval 14 day)) and (`a`.`is_current_revision` = 1) and (`a`.`is_deleted` = 0) and (`a`.`status_id` = 1))
I read through Dashboard and DashboardSelector.tpl. What parts do I replace with the name of my custom Views?

Instead of overwriting the default Views, can I create a new View in MySQL and replace the default display with my custom Views?

I know how to write the SQL to do that. I'll have different sections with criteria like:
Overdue
No case manager
Urgent

I don't see anything that denotes Case Manager or other role types in civicrm_case_contact. There's only id, case_id, contact_id.

[demeritcowboy]

Those views are intermediate queries used elsewhere as part of the bigger query that makes the dashboard (I think in CRM_Case_BAO_Case somewhere).

Case manager is a relationship defined in the xml for the case type. The relationship itself is in civicrm_relationship.

Going by memory, in the .tpl you don't reference the query/view, you reference the variables made available to smarty by the corresponding .php file (e.g. Dashboard.php). Usually in civicase the variable is named something like $rows, but if you look in the .tpl you'll see a {foreach} or something that will tell you.

[riddler]

I'm manually writing the MySQL queries. It will be embedded in a Wordpress Page (set to viewable by admins only) as a sort of 'front-end' Dashboard for the non-tech savvy admins.

http://code.neo.my/pt/hsvgd.php

Having problems with the Activity Type foreign key lookup. See the strings named Verizon and Tell a Friend. And showing the Activities under the same case. And possibly other errors that I have overlooked.

I won't be writing MySQL to update the cases directly. They'll click on the Manage case link leading to the page CiviCase.

I contacted the developer of CiviMobile Activities and Cases. He said development has not been started because their client hasn't needed it yet.

[Erik Hommel]

Are you sure you need CiviCase? I would imagine from your description you might just be happy with activities and a couple of views on them? That would probably make it a little simpler. You could use the Activity API to retrieve information about the activities? I'd recommend using the API rather than creating manual SQL queries?

[riddler]

The client's business logic fits CiviCase more. It's really similar to the Housing Support sample case. The person who created the Activity might forget to fill in the Schedule follow up part without CiviCase's business logic.

The report feature will be helpful for making reports like "x% of cases were resolved within a certain time", "response time to cases is less than y days", etc.

CiviCase has a built-in dashboard. That will be a fallback in case my 'front-end' has flaws. The Activities section is hidden unless you bookmark the search page.

If the manual SQL queries don't work out, I'll just customise the Dashboard with CSS and simple JavaScript if-statements to highlight the cases and hide unnecessary information that non tech-savvy people might find confusing.

It needs to be easy to maintain. I won't be doing the maintenance because I'm moving abroad. Some other volunteer (most likely fresh graduates who did 'IT' - Business Information Systems or similar) will take over. We don't have a lot of programmers here. Most people are in consultancy. They will be try to flog the client their homegrown CRM programs - for a fee.