CiviCRM Community Forums (archive)

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

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.

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.

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.