Basic activities reporting to campaign screen

[scardinius]

I'm working on feature which will add some basic activities reporting to campaign screen. We'd like to have a table with totals of all activities for current campaign and its children.

Requirements

• displaying selected activity types
  • fixed activity type - always show, even if data doesn't exist
  • invisible empties - if count=0, don't show this activity type
  • order by sequence
• displaying selected activity statuses to avoid huge result table with many empty cells
  • possibility to group statuses by label (solution for rare used statuses: Others)
  • order by sequence
• totals for each activity type and status
  • for current campaign and its children
  • by rows and columns
• configuration stored in own mysql tables for now (in order to avoid building complex configuration form)
• one configuration for all campaigns - assuming that activities of given type have only appropriate campaign_ids, it should do the job
• API with actions
  • calculating statuses
  • preparing report (grouping by labels)

Example

• Bulk Email is configured to be fixed, display even if data doesn't exist
• First Meeting has status Unreachable, second Meeting Available
• Outbound SMS doesn't occur in table because data doesn't exist
• Current campaign has several Phone Call activities but configuration table doesn't have this type

Result:

Type	Scheduled	Completed	Cancelled	Others	Total
Bulk Email (19)	0	0	0	0	0
Meeting (1)	0	0	0	2	2
Petition Signature (32)	154	368	52	0	574
Reminder Sent (40)	0	404	0	0	404
Total	154	772	52	2	980

Draft schema

CREATE TABLE config_activity_type (
  activity_type_id INT UNSIGNED PRIMARY KEY COMMENT 'Activity type which will be displayed',
  is_fixed INT UNSIGNED DEFAULT 0 COMMENT '0 : show only when data exists, 1 : show always'
);

CREATE TABLE config_activity_status (
  activity_type_id INT UNSIGNED COMMENT '',
  status_id INT UNSIGNED COMMENT 'Status which you want to display',
  grouping VARCHAR(64) NOT NULL COMMENT 'Statuses can by grouped by label. By default grouping is equal label of status',
  PRIMARY KEY (activity_type_id, status_id),
  KEY civicrm_campaign_config_activity_status_label_ind (grouping)
);

CREATE TABLE config_status_sequence (
  grouping VARCHAR(64) PRIMARY KEY ,
  sequence INT UNSIGNED NOT NULL
);

Example configuration

config_activity_type

activity_type_id	is_fixed
1 (Meeting)	0
4 (Outbound SMS)	0
19 (Bulk Email)	1
32 (Petition Signature)	0
40 (Reminder Sent)	0

config_activity_status

activity_type_id	status_id	grouping
1	1	Scheduled
1	2	Completed
1	3	Cancelled
1	4	Other
1	5	Other
1	6	Other
1	7	Other
1	8	Other

The same for other types

config_status_sequence

grouping	sequence
Scheduled	10
Completed	20
Cancelled	30
Left Message	40
Unreachable	50
Not Required	60
Available	70
No-show	80
Other	1000

What do you think @systopia ?

[bjendres]

great job!